Re: [GENERAL] query is taking longer time after a while
Bill Moran wrote: The OP did mention that he's using autovac, which will take care of both vacuum and analyze for him. However, he didn't provide his autovac config, and it happens at times that the defaults are not aggressive enough to keep a table well-maintained. Here are my autovac configuration parameters. I played around with them to make autovac work frequently. #autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 10 autovacuum_naptime = 10s autovacuum_vacuum_threshold = 2000 autovacuum_analyze_threshold = 2000 autovacuum_vacuum_scale_factor = 0.0005 autovacuum_analyze_scale_factor = 0.0005 #autovacuum_freeze_max_age = 2 #autovacuum_vacuum_cost_delay = 20 -- View this message in context: http://www.nabble.com/query-is-taking-longer-time-after-a-while-tp25661219p25675635.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] query is taking longer time after a while
Scott Marlowe-2 wrote: Just wondering, what version of pgsql are you running? I noticed a lot less degradation from heavily updated tables when I went to 8.3 and set the fill % for tables / indexes to 90% or so. If you're running an older version, the upgrade to 8.3 may well be worth the effort. I'm using version 8.3, but I don't know what you meant by fill %. Thanks, Tomer -- View this message in context: http://www.nabble.com/query-is-taking-longer-time-after-a-while-tp25661219p25675694.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] query is taking longer time after a while
In response to tomrevam : Scott Marlowe-2 wrote: Just wondering, what version of pgsql are you running? I noticed a lot less degradation from heavily updated tables when I went to 8.3 and set the fill % for tables / indexes to 90% or so. If you're running an older version, the upgrade to 8.3 may well be worth the effort. I'm using version 8.3, but I don't know what you meant by fill %. CopyPaste from the doc: ( http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html ) The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate. You can set this with this command: alter table foo set (fillfactor=90); Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - 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] Delphi connection ?
Raymond O'Donnell wrote: I fully agree I still use Delphi 6 a lot, and there's an ease of use about it that leaves other IDEs I've used in the shade. It's just a pity that it's so expensive Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- I trust you've investigated Lazarus? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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] Delphi connection ?
John R Pierce wrote: Nico Callewaert wrote: The thing you always hear about ODBC is, that it is very slow ? ADO is significantly faster than ODBC, so the preferred stack would be delphi - ado - postgres ole db - libpq -postgres I believe there exists a delphi-ado wrapper (at least my brief googling popped one up) Granted that this is a general query issue rather than one specific to Delphi/ODBC etc., but the major speed problem that I see is where I have a clause approximately like WHERE now() - datetime = '24 hours' repeated several times in a query (e.g. in subselects or views). The easiest solution is to replace this with a function returning an timestamp which is evaluated once during query execution. The moral of the story is that even if your queries are wrapped in a fancy program generated by something like Delphi, always leave yourself a way that you can extract a representative query after any parameterisation and do an EXPLAIN to look for inefficiencies. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- 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 Web Hosting
At 01:22 30/09/2009, Matt Friedman wrote: Hi, I'm trying to migrate a site to a new hosting company. The backend uses postgresql 8 and php. Anyone have thoughts on decent hosting companies for this sort of thing? I'm just looking at shared hosting as this isn't a resource intensive site. I use http://www.hub.org/ . They offer vps, web hosting and access to postgres 8.x and mysql databases. Thanks, Matt HTH Eduardo Morrás González Dept. I+D+i e-Crime Vigilancia Digital S21sec Labs Tlf: +34 902 222 521 Móvil: +34 555 555 555 www.s21sec.com, blog.s21sec.com Salvo que se indique lo contrario, esta información es CONFIDENCIAL y contiene datos de carácter personal que han de ser tratados conforme a la legislación vigente en materia de protección de datos. Si usted no es destinatario original de este mensaje, le comunicamos que no está autorizado a revisar, reenviar, distribuir, copiar o imprimir la información en él contenida y le rogamos que proceda a borrarlo de sus sistemas. Kontrakoa adierazi ezean, posta elektroniko honen barruan doana ISILPEKO informazioa da eta izaera pertsonaleko datuak dituenez, indarrean dagoen datu pertsonalak babesteko legediaren arabera tratatu beharrekoa. Posta honen hartzaile ez zaren kasuan, jakinarazten dizugu baimenik ez duzula bertan dagoen informazioa aztertu, igorri, banatu, kopiatu edo inprimatzeko. Hortaz, erregutzen dizugu posta hau zure sistemetatik berehala ezabatzea. Antes de imprimir este mensaje valora si verdaderamente es necesario. De esta forma contribuimos a la preservación del Medio Ambiente. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Where can I get the number of plans that considered by Planner?
Where can I add a integer counter to count the plans considered by planner. In my opinion, it is in the src/backend/optimizer/path directorty.
Re: [GENERAL] Collation in ORDER BY not lexicographical
On Tue, 2009-09-29 at 03:21 -0600, Scott Marlowe wrote: On Tue, Sep 29, 2009 at 2:52 AM, Paul Gaspar devl...@revolversoft.com wrote: Hi! We have big problems with collation in ORDER BY, which happens in binary order, not alphabetic (lexicographical), like:. A B Z a z Ä Ö ä ö PG is running on Mac OS X 10.5 and 10.6 Intel. I seem to recall there were some problem with Mac locales at some point being broken. Could be you're running into that issue. Yes, the UTF8 locales on BSD systems (Mac OS X, FreeBSD, etc.) are dysfunctional. Either switch to a non-UTF8 locale or a different operating system. -- 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 Macro Query
Hi there, I have written some code to calculate basic summary stats and wish to then incorporate this code within a macro... I want to run the code many multiple times and define the variables (on which the summary statistics are calculated) outside of the basic code. For example, see some very basic code below... I would use a macro so that I don't have to change 'STUDY_GROUP' in the code each time. select STUDY_GROUP , count(STUDY_GROUP) from TABLE1 group by STUDY_GROUP; Many thanks in advance,
Re: [GENERAL] PostgreSQL Macro Query
On Wed, Sep 30, 2009 at 11:13:06AM +0100, Bronagh Grimes wrote: I have written some code to calculate basic summary stats and wish to then incorporate this code within a macro... I want to run the code many multiple times and define the variables (on which the summary statistics are calculated) outside of the basic code. I'd normally do this outside Postgres, maybe from something like Python or Perl. For example, see some very basic code below... I would use a macro so that I don't have to change 'STUDY_GROUP' in the code each time. If you want to stay inside PG and use psql, you could rewrite the query to make this less of a problem. select STUDY_GROUP , count(STUDY_GROUP) from TABLE1 group by STUDY_GROUP; SELECT STUDY_GROUP, COUNT(*) FROM TABLE1 GROUP BY 1; Will do the same thing; except where the column is NULL. You will have been getting zero before, but now you'll be told how many null entries you have--this may or may not be what you want. If you don't want it, you could do: SELECT v, COUNT(v) FROM (SELECT STUDY_GROUP AS v FROM TABLE1) x GROUP BY v; There would still only be one name to change then and PG would optimize the query to do the same thing either way so performance shouldn't be affected. Hope that helps! -- Sam http://samason.me.uk/ -- 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 Web Hosting
On Tue, 2009-09-29 at 19:22 -0400, Matt Friedman wrote: I'm trying to migrate a site to a new hosting company. The backend uses postgresql 8 and php. Anyone have thoughts on decent hosting companies for this sort of thing? Here is the list at postgresql.org : http://www.postgresql.org/support/professional_hosting -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] query is taking longer time after a while
In response to tomrevam to...@fabrix.tv: My apologies, I should have asked for the output of VACUUM VERBOSE on the problem table in conjunction with these settings. (make sure you do VACUUM VERBOSE when the table is exhibiting the speed problem) Bill Moran wrote: The OP did mention that he's using autovac, which will take care of both vacuum and analyze for him. However, he didn't provide his autovac config, and it happens at times that the defaults are not aggressive enough to keep a table well-maintained. Here are my autovac configuration parameters. I played around with them to make autovac work frequently. #autovacuum = on log_autovacuum_min_duration = 0 autovacuum_max_workers = 10 autovacuum_naptime = 10s autovacuum_vacuum_threshold = 2000 autovacuum_analyze_threshold = 2000 autovacuum_vacuum_scale_factor = 0.0005 autovacuum_analyze_scale_factor = 0.0005 #autovacuum_freeze_max_age = 2 #autovacuum_vacuum_cost_delay = 20 -- View this message in context: http://www.nabble.com/query-is-taking-longer-time-after-a-while-tp25661219p25675635.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] query is taking longer time after a while
In response to Vick Khera vi...@khera.org: On Tue, Sep 29, 2009 at 9:48 AM, Bill Moran wmo...@potentialtech.com wrote: There has (over the last few years) been a lot of speculation from people who think that indexes may suffer performance degradation under some workloads. I've yet to see any actual evidence. Just last week I reindexed a 70+ million row table and shaved about 50% of the bloated index pages on a two integer column index. I believe it hadn't been reindexed in about 6 months. Right. I've seen the same kind of thing with our Bacula databases. I regularly have to re-index for performance purposes. My data lives on most tables for about 6 months and is then deleted, or lives forever and is updated frequently. This is the part that I've yet to see, is any actual demonstration that this makes a _performance_ difference. The space saving is well known and easy to demonstrate, but in my own tests, whether or not doing a REINDEX has any appreciable performance improvement has never been clear. It stands to reason that it will, but I've just never seen demonstrated. It's quite possible that I simply missed the discussion thread on which this was shown. I'm not arguing with you. I'm simply curious as to whether index bloat could cause the magnitude of performance problem the OP is having, and I don't have any personal experience with that degree of problem, and I've not heard it from anyone else, either. In my personal experience, REINDEX seems to provide only a nominal improvement, but it's likely that I'm not seeing the worst case that others are describing. -- 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] do I need a rollback() after commit that fails?
On 30 Sep 2009, at 4:01, Vick Khera wrote: The question still stands: if the COMMIT fails, ROLLBACK is not required in Postgres. Is this portable to other databases? I don't think so. I recall messages on this list claiming that some databases (MS SQL, MySQL if memory serves me) commit the queries up to the failed query anyway if you issue a COMMIT (which is just wrong!), so the commit succeeds and there's nothing to rollback after that. Some searching should turn up those messages, if I recall correctly the issue at hand was that people expected that behaviour in Postgres too. But I don't know what Perl DBI does internally when issuing $dbh- commit(), maybe it's taking such things into account already. Alban Hertroys -- Screwing up is the best way to attach something to the ceiling. !DSPAM:737,4ac356da11681178911724! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Updating row with updating function, bug or feature?
Hello List, I've run into some weirdness in PSQL 8.3.8 (Ubuntu 8.04 LTS x86_64 package). When I update a row while using a function result that updates that very same row in the WHERE part of the update, the main update no longer takes place, even though the WHERE conditions should match. But if I execute the function before the update, and then do the update based on the same logic, I see both changes. Is this a bug, a feature or something else entirely? Please CC replies to me as well, as I am not on the list. The following script illustrates the problem: == SCRIPT == BEGIN; CREATE TABLE test ( id INTEGER PRIMARY KEY, locked BOOLEAN DEFAULT FALSE, accessed TIMESTAMP WITH TIME ZONE ); CREATE OR REPLACE FUNCTION lock(INTEGER) RETURNS BOOLEAN AS $$ BEGIN UPDATE test SET locked=TRUE WHERE id = $1 AND NOT locked; RAISE NOTICE 'lock: % - %', $1, FOUND; RETURN FOUND; END; $$ LANGUAGE plpgsql VOLATILE; INSERT INTO test (id) VALUES(1); INSERT INTO test (id) VALUES(2); SELECT 'accessed is not set'; UPDATE test SET accessed=now() WHERE id=1 AND CASE WHEN id=1 THEN lock(1) ELSE FALSE END; SELECT * FROM test; SELECT 'accessed is set'; SELECT lock(2); UPDATE test SET accessed=now() WHERE id=2 AND locked; SELECT * FROM test; ROLLBACK; == END SCRIPT == == OUTPUT == CREATE TABLE CREATE FUNCTION INSERT 0 1 INSERT 0 1 ?column? - accessed is not set (1 row) psql:bugfeat.sql:26: NOTICE: lock: 1 - t UPDATE 0 id | locked | accessed ++-- 2 | f | 1 | t | (2 rows) ?column? - accessed is set (1 row) psql:bugfeat.sql:31: NOTICE: lock: 2 - t lock -- t (1 row) UPDATE 1 id | locked | accessed ++--- 1 | t | 2 | t | 2009-09-30 15:27:20.497355+02 (2 rows) ROLLBACK == END OUTPUT == Thanks 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] Where can I get the number of plans that considered by Planner?
=?UTF-8?B?57qq5pmT5pum?= sheep...@gmail.com writes: Where can I add a integer counter to count the plans considered by planner. Well, you could count the number of calls to add_path, but a path is hardly the same thing as a complete plan. 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] ms-sql - pg 8.x
Hello, It seems that very shortly I will have to extract some data from ms-sql server and load it into postgres. It seems that the ms-sql is getting bigger with increasingly more users and performance is getting worse n worse. And a local copy of data is required for and application that needs pull lots of small bits of data back quickly complex queries tho, currently embedded in views. And if the data is no older than 24 hours that is OK. Although I am pretty useful with sql on Oracle and mysql this is a bit outside my comfort zone. Could someone provide a few pointers on how to go about this and what the issues might be? The first isssue that occurs to me is that CP1252 is used throughout the data and there is a lot of european special characters, e acute for example. But the column names etc are regular chars [a-zA-Z]. All links and hints gratefully received! Syd Send instant messages to your online friends http://uk.messenger.yahoo.com
Re: [GENERAL] INSERT with RETURNING clause inside SQL function
you can try: CREATE OR REPLACE FUNCTION add_something(text, text) RETURNS integer AS $BODY$ DECLARE somevariable integer; BEGIN INSERT INTO sometable (id, foo, bar ) VALUES (DEFAULT, $1, $2 ) RETURNING id INTO somevariable; return somevariable; END;$BODY$ LANGUAGE 'plpgsql' VOLATILE; -- View this message in context: http://www.nabble.com/INSERT-with-RETURNING-clause-inside-SQL-function-tp20312197p25678362.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Updating row with updating function, bug or feature?
Thomas Jacob ja...@internet24.de writes: I've run into some weirdness in PSQL 8.3.8 (Ubuntu 8.04 LTS x86_64 package). When I update a row while using a function result that updates that very same row in the WHERE part of the update, the main update no longer takes place, even though the WHERE conditions should match. But if I execute the function before the update, and then do the update based on the same logic, I see both changes. This is expected; it's worked like that since Berkeley days. An UPDATE will not touch a row that's already been updated within your own transaction since the UPDATE started. This is mainly to avoid sorceror's-apprentice syndrome with repeatedly updating the same row. In general, having side-effects in a function invoked in WHERE is a dangerous and unwise practice anyhow, IMNSHO. You have very little control over when or even whether the side effects will happen. In the particular case at hand, you might want to think about using SELECT FOR UPDATE locking instead of rolling your own. Something like BEGIN; SELECT * FROM tab WHERE id = x FOR UPDATE; ... do some work using retrieved values ... UPDATE tab SET ... WHERE id = x; COMMIT; has simple and reliable behavior. 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] ms-sql - pg 8.x
Sydney Puente wrote: Hello, It seems that very shortly I will have to extract some data from ms-sql server and load it into postgres. It seems that the ms-sql is getting bigger with increasingly more users and performance is getting worse n worse. And a local copy of data is required for and application that needs pull lots of small bits of data back quickly complex queries tho, currently embedded in views. And if the data is no older than 24 hours that is OK. Although I am pretty useful with sql on Oracle and mysql this is a bit outside my comfort zone. Could someone provide a few pointers on how to go about this and what the issues might be? MS SQL Server has a pretty useful Data Translation Engine aka DTE, which can be used to export data on a scheduled and scripted batch basis to external databases... You'd need to establish a postgres client with OLEDB/ADODB or ODBC support on the SQL Server to do it this way. Alternately, you'd run a script on the SQL Server (or any client that has access to it) and export the data you want to CSV file(s), then import these into a postgres database. Where things might get messy if this data is large and growing as you say, doing a full copy like this could be very time consuming on a daily basis.Doing incremental replication between dissimilar databases is not at all easy. The first isssue that occurs to me is that CP1252 is used throughout the data and there is a lot of european special characters, e acute for example. But the column names etc are regular chars [a-zA-Z]. CP1252 aka Windows-1252 is actually pretty close to ISO-8859-1 aka LATIN1. The differences are mostly that CP1252 uses the 80-9F section for additional characters, this is unused in LATIN1. Personally, I'd probably make the Postgres database UTF-8, then use Windows-1252 as the client_encoding during the import process. -- 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] Updating row with updating function, bug or feature?
On Wed, 2009-09-30 at 10:17 -0400, Tom Lane wrote: Thomas Jacob ja...@internet24.de writes: I've run into some weirdness in PSQL 8.3.8 (Ubuntu 8.04 LTS x86_64 package). When I update a row while using a function result that updates that very same row in the WHERE part of the update, the main update no longer takes place, even though the WHERE conditions should match. But if I execute the function before the update, and then do the update based on the same logic, I see both changes. This is expected; it's worked like that since Berkeley days. An UPDATE will not touch a row that's already been updated within your own transaction since the UPDATE started. This is mainly to avoid sorceror's-apprentice syndrome with repeatedly updating the same row. OK , thanks for clearing this up. Out of interest, does some SQL standard make any clear pronouncements on conforming behavior in this case? In the particular case at hand, you might want to think about using SELECT FOR UPDATE locking instead of rolling your own. Something like BEGIN; SELECT * FROM tab WHERE id = x FOR UPDATE; ... do some work using retrieved values ... UPDATE tab SET ... WHERE id = x; COMMIT; has simple and reliable behavior. I need to lock a row over longer periods, just for an application, without staying connected to the database, or indeed for the database system to still be running. So SELECT FOR UPDATE isn't enough. To get the desired functionality, I simply moved the updates and checks from the function to the toplevel updates, and then everything works fine. Thanks for your quick reply, Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] automated row deletion
I am inserting 250 rows of data (~2kbytes/row) every 5 seconds into a table (the primary key is a big serial). I need to be able to limit the size of the table to prevent filling up the disk. Is there a way to setup the table to do this automatically or do I have to periodically figure out how many rows are in the table and delete the oldest rows manually? Thanks, Dave This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message. THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity. WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
Re: [GENERAL] automated row deletion
Dave Huber wrote: I am inserting 250 rows of data (~2kbytes/row) every 5 seconds into a table (the primary key is a big serial). I need to be able to limit the size of the table to prevent filling up the disk. Is there a way to setup the table to do this automatically or do I have to periodically figure out how many rows are in the table and delete the oldest rows manually? I think you'll find row deletes would kill your performance. For time aged data like that, we use partitioned tables, we typically do it by the week (keeping 6 months of history), but you might end up doing it by N*1000 PK values or some such, so you can use your PK to determine the partition. With a partitioning scheme, its much faster to add a new one and drop the oldest at whatever interval you need. See http://www.postgresql.org/docs/current/static/ddl-partitioning.html -- 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] automated row deletion
I think there no better way you can get around this problem. You need to check the disk periodically and it is not to hard. 2009/10/1 Dave Huber dhu...@letourneautechnologies.com I am inserting 250 rows of data (~2kbytes/row) every 5 seconds into a table (the primary key is a big serial). I need to be able to limit the size of the table to prevent filling up the disk. Is there a way to setup the table to do this automatically or do I have to periodically figure out how many rows are in the table and delete the oldest rows manually? Thanks, Dave -- This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message. THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity. WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
Re: [GENERAL] ms-sql - pg 8.x
John R Pierce pie...@hogranch.com writes: Sydney Puente wrote: The first isssue that occurs to me is that CP1252 is used throughout the data and there is a lot of european special characters, e acute for example. But the column names etc are regular chars [a-zA-Z]. CP1252 aka Windows-1252 is actually pretty close to ISO-8859-1 aka LATIN1. The differences are mostly that CP1252 uses the 80-9F section for additional characters, this is unused in LATIN1. Personally, I'd probably make the Postgres database UTF-8, then use Windows-1252 as the client_encoding during the import process. FWIW, we do support win1252 as a database encoding. I tend to agree that switching to something better-standardized would be a good idea though. 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] error message and documentation
Hi all, I use PostgresSQL 8.3 through JDBC Recently one transaction has failed with the following error message: Detail: Process 10660 waits for AccessShareLock on relation 36036 of database 34187; blocked by process 2212. Process 2212 waits for AccessExclusiveLock on relation 36044 of database 34187; blocked by process 10660. I'm trying to understand why I can have this kind or error (it is probably some programming mistake) but reading the PostgresSQL manual I cannot find any trace of AccessExclusiveLock , while I have found explanation of what AccessShareLock is. First question: is there a problem in the documentation or in PostgresSQL error messages ? Another question. The message above was reported to explain why a query sent to the db server has failed: am I right saying that, looking at the above error message, Process 10660 was the one executing the query that has failed and Process 2212 was executing something else and kept going its way ? Final question: is there a way to know what query a Process has executed ? I'm thinking of some logging configuration for PostgresSQL. TIA to all of you. -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- 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 message and documentation
Ivano Luberti lube...@archicoop.it writes: I'm trying to understand why I can have this kind or error (it is probably some programming mistake) but reading the PostgresSQL manual I cannot find any trace of AccessExclusiveLock , while I have found explanation of what AccessShareLock is. First question: is there a problem in the documentation or in PostgresSQL error messages ? Not sure where you are looking, but they are all explained at http://www.postgresql.org/docs/8.3/static/explicit-locking.html#LOCKING-TABLES Another question. The message above was reported to explain why a query sent to the db server has failed: am I right saying that, looking at the above error message, Process 10660 was the one executing the query that has failed and Process 2212 was executing something else and kept going its way ? I don't recall whether there's any particular guarantee about which process in the Detail message is the one that gets the error. But since these are asking for two different lock levels it shouldn't be that hard to figure out which is which. Also, the failing query really should have been included as another field of the error report. If you're using client code that prints the detail field and not the query field, you might want to revisit that decision. Final question: is there a way to know what query a Process has executed ? I'm thinking of some logging configuration for PostgresSQL. http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html 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] error message and documentation
Tom, thanks for your answer: the reason I failed to find AccessExclusiveLock is beacuse that string of character is never written in the manual while AccessShareLock is written as it is written above in the manual in the section about index lockin. Not knowing the manual in detail I didn't know there is this section 13.3.1. About which process has failed you say: I don't recall whether there's any particular guarantee about which process in the Detail message is the one that gets the error. But since these are asking for two different lock levels it shouldn't be that hard to figure out which is which. Also, the failing query really should have been included as another field of the error report. If you're using client code that prints the detail field and not the query field, you might want to revisit that decision. My problem is I know what query has failed , but I don't know the other one that caused the deadlock condition. A few second later the same query run by another process (procpid 11704) failed again conflicting again with the process with procpid 2212. Since processes represents the connections open in a small pool that uses jdbc driver, either that was a long query that locked out the failed queries or the 2212 was reused by another application process and coincidentally caused another deadlock. In fact another thing I was asking myself is if exists a way from my java application to know which java thread is using a given postgresSQL process. Because the cause of the deadlock is clearly I have concurrent thread that can generate conflicts on the db. But I think this is more a JDBC list question. Thanks again. -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- 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 message and documentation
Ivano Luberti lube...@archicoop.it writes: My problem is I know what query has failed , but I don't know the other one that caused the deadlock condition. Ah. Is it practical for you to upgrade to PG 8.4? IIRC the deadlock reporting code got improved in 8.4 to log all the queries involved. 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] error message and documentation
I don't know: I'm not subscribed to the anno9unce list so I was not aware 8.4 has now a production release. In the past upgrading from 8.2 to 8.3 solved a big issues but at the time the application is not in production. So we are going to evaluate this option. Anyway after reading the manual in the section you pointed out I have been able to identify the other process involved in deadlock: we are going to analyze when the two threads can conflict. I suspect we will have to use the list again... Regards Tom Lane ha scritto: Ivano Luberti lube...@archicoop.it writes: My problem is I know what query has failed , but I don't know the other one that caused the deadlock condition. Ah. Is it practical for you to upgrade to PG 8.4? IIRC the deadlock reporting code got improved in 8.4 to log all the queries involved. regards, tom lane -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] I can not drop a user/role because an object depent on it.
Dear my friends I can not drop a user because another object need it. How can I know which object need it? I really want to drop everything inside my PostgreSQL, to reset my installation really from beginning. postgres=# drop user ivia; FEHLER: kann Rolle »ivia« nicht löschen, weil andere Objekte davon abhängen DETAIL: Eigentümer von Datenbank iVia 7 Objekte in Datenbank iVia--mytranslation: Error: can not delete Role »ivia«, because another object depend on it DETAIL: owner of Database iVia. postgres=# I've dropped the database iVia. Thank you very much in advance. -- 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] I can not drop a user/role because an object depent on it.
In response to Ricky Tompu Breaky ricky.bre...@uni.de: Dear my friends I can not drop a user because another object need it. How can I know which object need it? I really want to drop everything inside my PostgreSQL, to reset my installation really from beginning. The easiest way to accomplish this is to stop postgres, delete the postgres data directory and rerun initdb. -- 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
[GENERAL] Weird behavior with sensitive cursors.
Hi -
I'm seeing a behavior with updatable cursors that matches neither the
behavior
of a sensitive cursor nor an insensitive one. In summary, I'm running with
serializable as the isolation level and rows updated within the same
transaction seem to disappear under the cursor.
From the postgres documentation (I'm using 8.3.0), specifying FOR UPDATE
should
provide the client with a sensitive cursor: If the cursor's query includes
FOR UPDATE or FOR SHARE, then returned rows are locked at the time they are
first fetched, in the same way as for a regular SELECT command with these
options. In addition, the returned rows will be the most up-to-date
versions;
therefore these options provide the equivalent of what the SQL standard
calls a
sensitive cursor.
But then I get this behavior:
{{{
SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET
begin;
BEGIN
show transaction isolation level;
transaction_isolation
---
serializable
(1 row)
create table foo (a bigint);
CREATE TABLE
insert into foo select generate_series(0, 9);
INSERT 0 10
select * from foo;
a
---
0
1
2
3
4
5
6
7
8
9
(10 rows)
declare c1 no scroll cursor for select * from foo for update;
DECLARE CURSOR
update foo set a=1000 where a5;
UPDATE 4
fetch all from c1;
a
---
0
1
2
3
4
5
(6 rows)
select * from foo;
a
--
0
1
2
3
4
5
1000
1000
1000
1000
(10 rows)
abort;
ROLLBACK
}}}
Based on my interpretation of cursor sensitivity, I should:
* See rows 0 through 9 if the cursor is insensitive. In fact, this is what
I
get if I remove the FOR UPDATE option.
* See the same as a SELECT command executed within the same transaction if
the
cursor is sensitive.
This seems like a bug to me, and it prevents one from getting sensitive
cursors
with postgres. Can anybody explain the behavior above?
thanks a lot,
-daniel
Re: [GENERAL] Weird behavior with sensitive cursors.
Daniel F escribió: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE; begin; show transaction isolation level; create table foo (a bigint); insert into foo select generate_series(0, 9); declare c1 no scroll cursor for select * from foo for update; update foo set a=1000 where a5; fetch all from c1; Interesting. If I create an non-unique index on the table before declaring the cursor, FETCH throws an error: alvherre=# fetch all from c1; ERROR: attempted to lock invisible tuple -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Performance evaluation of PostgreSQL's historic releases
2009/9/29 Amitabh Kant amitabhk...@gmail.com On Tue, Sep 29, 2009 at 1:06 PM, György Vilmos vilmos.gyo...@gmail.comwrote: Hello, I've done a benchmark of recent versions of PostgreSQL's last five major releases to see, how performance has changed during the past years from version to version. You can find the article here: http://suckit.blog.hu/2009/09/26/postgresql_history Thanks for working on this great piece of software! -- http://suckit.blog.hu/ Hello Thanks for the benchmark. Did you configure FreeBSD kernel parameters too, or used the default values? kern.ipc.shmall=393216 kern.ipc.shmmax=1610612736 kern.ipc.semmap=256 kern.ipc.shm_use_phys=1 kern.smp.topology=8 kern.timecounter.smp_tsc=1 kern.timecounter.hardware=TSC smp.topology was needed because the current kernel doesn't recognize the correct cache topology on this machine. -- http://suckit.blog.hu/
Re: [GENERAL] Performance evaluation of PostgreSQL's historic releases
2009/9/29 Grzegorz Jaśkiewicz gryz...@gmail.com any chance you can test the recent postgresql Cvs-head build (to be 8.5). Sadly, no, the machine was not mine and I had to give it back. -- http://suckit.blog.hu/
Re: [GENERAL] Performance evaluation of PostgreSQL's historic releases
2009/9/30 Greg Smith gsm...@gregsmith.com On Tue, 29 Sep 2009, Gy?rgy Vilmos wrote: I've done a benchmark of recent versions of PostgreSQL's last five major releases to see, how performance has changed during the past years from version to version. Your comments suggest V8.4 moves backwards as far as performance goes, which is a bit misleading. A more fair characterization would be to disclaim 8.4 as potentially being slower on the very simple benchmarks you ran, not necessarily in general. What actually happened is some features were retuned to give better results on difficult queries (increasing default_statistics_target is the main example there), and one of the major maintenance tasks was removed (adjusting the max_fsm_* parameters). These and the other 8.4 changes that touched performance added a small amount of overhead for simple queries, but in the situations where they help the gain can be big. Had you instead benchmarked a complicated query where the statistics change caused the default behavior to provide better query plans, or you had a deletion-heavy workload where 8.3 had trouble maintaining database free space, you could have seen significantly better performance on 8.4. The improvements in that version just don't help trivial examples like the sysbench ones you ran. P.S. On your write-heavy tests, increasing checkpoint_segments a lot should improve overall performance, if you re-test at some point. Thank you very much for the valuable comments, I will keep them in mind for the next test. BTW, this wasn't a how could I get the maximum out of PostgreSQL, that would need much more time and research (or inner knowledge about the program). -- http://suckit.blog.hu/
Re: [GENERAL] Performance evaluation of PostgreSQL's historic releases
2009/9/30 Scott Marlowe scott.marl...@gmail.com On Tue, Sep 29, 2009 at 4:47 PM, Greg Smith gsm...@gregsmith.com wrote: P.S. On your write-heavy tests, increasing checkpoint_segments a lot should improve overall performance, if you re-test at some point. Just wanted to add that in order to really test a db, you need a benchmark that runs a lot longer than a few minutes. I routinely use tests that run for hours to get an idea how things like write ahead logging and such affect the db under heavy, long term load. Agreed, but even with 5x1 minutes of test runs for each threads it took a week to do all the tests. I've once heard that a company have spent three man years for performance testing one of its products... -- http://suckit.blog.hu/
Re: [GENERAL] I can not drop a user/role because an object depent on it.
You've solved my Problem, Bill. I thank you very much, my friend Bill Moran. Highly appreciated. On Wed, 30 Sep 2009 13:45:52 -0400 Bill Moran wmo...@potentialtech.com wrote: In response to Ricky Tompu Breaky ricky.bre...@uni.de: Dear my friends I can not drop a user because another object need it. How can I know which object need it? I really want to drop everything inside my PostgreSQL, to reset my installation really from beginning. The easiest way to accomplish this is to stop postgres, delete the postgres data directory and rerun initdb. -- 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] I can not drop a user/role because an object depent on it.
On Wednesday 30 September 2009 10:43:35 am Ricky Tompu Breaky wrote: Dear my friends I can not drop a user because another object need it. How can I know which object need it? I really want to drop everything inside my PostgreSQL, to reset my installation really from beginning. postgres=# drop user ivia; FEHLER: kann Rolle »ivia« nicht löschen, weil andere Objekte davon abhängen DETAIL: Eigentümer von Datenbank iVia 7 Objekte in Datenbank iVia--mytranslation: Error: can not delete Role »ivia«, because another object depend on it DETAIL: owner of Database iVia. postgres=# I've dropped the database iVia. Thank you very much in advance. To clarify did you try to DROP USER ivia before or after you dropped the database iVia ? -- Adrian Klaver akla...@comcast.net -- 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] Weird behavior with sensitive cursors.
Alvaro Herrera alvhe...@commandprompt.com writes: Interesting. If I create an non-unique index on the table before declaring the cursor, FETCH throws an error: alvherre=# fetch all from c1; ERROR: attempted to lock invisible tuple I get that in 8.4 and HEAD even without any index, just trying the given case. It looks to me like this is a bug in the new snapshot management. The cursor is using CurrentSnapshot directly --- it does not have a private copy --- and therefore when CommandCounterIncrement happens it affects what the cursor can see. The cursor should not be able to see any tuples created after it was created. I think we need to ensure that when a cursor is created, it obtains a private copy of the current snapshot ... but I'm not sure where that ought to happen. Thoughts? 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] Weird behavior with sensitive cursors.
Tom Lane escribió: Alvaro Herrera alvhe...@commandprompt.com writes: Interesting. If I create an non-unique index on the table before declaring the cursor, FETCH throws an error: alvherre=# fetch all from c1; ERROR: attempted to lock invisible tuple I get that in 8.4 and HEAD even without any index, just trying the given case. It looks to me like this is a bug in the new snapshot management. The cursor is using CurrentSnapshot directly --- it does not have a private copy --- and therefore when CommandCounterIncrement happens it affects what the cursor can see. The cursor should not be able to see any tuples created after it was created. Right, but what if it tries to grab a tuple that was updated? When the portal tries to fetch the tuple, it has become invisible, thus the error, no? I think we need to ensure that when a cursor is created, it obtains a private copy of the current snapshot ... but I'm not sure where that ought to happen. Thoughts? Maybe you are right, but I don't think that's the only bug here. I think the right place is PerformCursorOpen; instead of having PortalStart use GetActiveSnapshot(), it should create a copy of it. The first problem is that we don't have any API in snapmgr.c for this gimme a private copy of this snap; I think we'll have to open up CopySnapshot to outside calls :-( I played a bit with doing this only when the OPT_CURSOR_INSENSITIVE bit is set, but I'm not ever seeing it set -- with or with FOR UPDATE ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Weird behavior with sensitive cursors.
Alvaro Herrera escribió: I played a bit with doing this only when the OPT_CURSOR_INSENSITIVE bit is set, but I'm not ever seeing it set -- with or with FOR UPDATE ... Oh, I see, that's a grammar only bit. I need to check rowMarks == NIL instead. It doesn't help anyway but at least I figured that bit out :-( -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Weird behavior with sensitive cursors.
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: I think we need to ensure that when a cursor is created, it obtains a private copy of the current snapshot ... but I'm not sure where that ought to happen. Thoughts? Maybe you are right, but I don't think that's the only bug here. Well, the first problem is that 8.4 is failing to duplicate the historical behavior. After that we can think about whether we'd like to change the historical behavior. I'm not entirely convinced about that yet (and it certainly wouldn't be something I'd want to back-patch). 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] Weird behavior with sensitive cursors.
Tom Lane escribió:
Alvaro Herrera alvhe...@commandprompt.com writes:
Tom Lane escribi�:
I think we need to ensure that when a cursor is created, it obtains a
private copy of the current snapshot ... but I'm not sure where that
ought to happen. Thoughts?
Maybe you are right, but I don't think that's the only bug here.
Well, the first problem is that 8.4 is failing to duplicate the
historical behavior.
Oh! That's easy.
After that we can think about whether we'd like to
change the historical behavior. I'm not entirely convinced about that
yet (and it certainly wouldn't be something I'd want to back-patch).
I don't care enough about that to spend much time on it ...
--
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
*** src/backend/commands/portalcmds.c 11 Jun 2009 14:48:56 - 1.79
--- src/backend/commands/portalcmds.c 1 Oct 2009 01:24:11 -
***
*** 47,52
--- 47,53
DeclareCursorStmt *cstmt = (DeclareCursorStmt *) stmt-utilityStmt;
Portal portal;
MemoryContext oldContext;
+ Snapshot snapshot;
if (cstmt == NULL || !IsA(cstmt, DeclareCursorStmt))
elog(ERROR, PerformCursorOpen called for non-cursor query);
***
*** 118,127
portal-cursorOptions |= CURSOR_OPT_NO_SCROLL;
}
/*
* Start execution, inserting parameters if any.
*/
! PortalStart(portal, params, GetActiveSnapshot());
Assert(portal-strategy == PORTAL_ONE_SELECT);
--- 119,135
portal-cursorOptions |= CURSOR_OPT_NO_SCROLL;
}
+ /* Set up snapshot for portal */
+ if (stmt-rowMarks != NIL)
+ snapshot = RegisterCopiedSnapshot(GetActiveSnapshot(),
+ portal-resowner);
+ else
+ snapshot = GetActiveSnapshot();
+
/*
* Start execution, inserting parameters if any.
*/
! PortalStart(portal, params, snapshot);
Assert(portal-strategy == PORTAL_ONE_SELECT);
*** src/backend/utils/time/snapmgr.c 11 Jun 2009 14:49:06 - 1.10
--- src/backend/utils/time/snapmgr.c 1 Oct 2009 01:19:51 -
***
*** 385,390
--- 385,403
}
/*
+ * As above, but create a new, independeny copy of the snapshot.
+ */
+ Snapshot
+ RegisterCopiedSnapshot(Snapshot snapshot, ResourceOwner owner)
+ {
+ if (snapshot == InvalidSnapshot)
+ return InvalidSnapshot;
+
+ snapshot = CopySnapshot(snapshot);
+ return RegisterSnapshotOnOwner(snapshot, owner);
+ }
+
+ /*
* UnregisterSnapshot
*
* Decrement the reference count of a snapshot, remove the corresponding
*** src/include/utils/snapmgr.h 11 Jun 2009 14:49:13 - 1.5
--- src/include/utils/snapmgr.h 1 Oct 2009 01:20:12 -
***
*** 36,41
--- 36,42
extern Snapshot RegisterSnapshot(Snapshot snapshot);
extern void UnregisterSnapshot(Snapshot snapshot);
extern Snapshot RegisterSnapshotOnOwner(Snapshot snapshot, ResourceOwner owner);
+ extern Snapshot RegisterCopiedSnapshot(Snapshot snapshot, ResourceOwner owner);
extern void UnregisterSnapshotFromOwner(Snapshot snapshot, ResourceOwner owner);
extern void AtSubCommit_Snapshot(int level);
--
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] Weird behavior with sensitive cursors.
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane escribió: Well, the first problem is that 8.4 is failing to duplicate the historical behavior. Oh! That's easy. I don't think that testing rowMarks is the right thing at all here. That tells you whether it's a SELECT FOR UPDATE, but actually we want any cursor (and only cursors) to have a private snapshot. Also, do we really need the Register bit? Won't the portal register its use of the snapshot anyway (or if it doesn't, isn't that a bug)? 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
