Re: [HACKERS] Extra cost of lossy mode Bitmap Scan plan
Hi. I apologize for this late reply. Tom Lane wrote: I think it's probably useless. In the first place, at reasonable values of work_mem the effect is going to be negligible (in the sense that a plain indexscan would never win). In the second place, there isn't any way to guess the extent of lossiness at plan time --- it depends on how much the target rows are clumped on particular pages. The planner hasn't got any stats that would let it guess that, and even if we tried to collect such stats they'd probably be too unstable to be useful. Thank you. I understand your two points. But in this context, I can't understand why Bitmap Scan is faster than Index Scan. If there are many where conditions like following, I can understand why Bitmap Scan is faster than Index Scan. select * from emp where emp_no 1 and sex = 1 and salary 5000; Bitmap Scan merges each result bitmaps and after the merge read tuples. But I don't understand this case. select * from emp where emp_no 1; Is Bitmap Scan is faster than Index Scan in this case ? If so, why ? My understanding that both Index Scan and Bitmap Scan scans index on emp_no and they are almost same cost. In addition Bitmap Scan creates a bitmap. So Bitmap Scan is a little bit slower? Best regards, - MINOWA Taro (Higepon) Cybozu Labs, Inc. http://www.monaos.org/ http://code.google.com/p/mosh-scheme/ On Tue, Apr 28, 2009 at 11:36 PM, Greg Stark st...@enterprisedb.com wrote: On Tue, Apr 28, 2009 at 3:02 PM, Robert Haas robertmh...@gmail.com wrote: You may be right, but on the other hand, I'm not sure there's any sense in NOT trying to model the impact of the additional heap fetches. Yeah, the flip side of the argument is that we generally try to do the best job we can modeling costs and let the arithmetic work out how it however it does because you never know what kind of wacky situations will arise planning queries and and the better the estimates the better your chance of coming up with a good plan. For example the planner may have other join orders which allow it to avoid accessing those records entirely. So the comparison with a nested loop might not be the only comparison that matters. It might be a case of whether to run a bitmap scan against this table or some scan against another table to drive the join. I have been running benchmarks comparing bitmap heap scans against index scans amongst other comparisons. I haven't done CVS head yet but on an older version I'm seeing with effective_io_concurrency set to 0 scanning 1000 random tuples throughout a 130G table (one searched tuple per page) on a machine with 64G of ram after repeated executions index scans settle down to about 245s vs 205s for bitmap scans (for 100 iterations). So they're about 16% faster for this use case. Incidentally with effective_io_concurrency set to 30 on this 30-drive raid the bitmap scans go down to 17s :) -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for TODO Item: Add prompt escape to display the client and server versions
On Thursday 07 May 2009 05:23:41 Dickson S. Guedes wrote: This is a WIP patch (for the TODO item in the subject) that I'm putting in the Commit Fest queue for 8.5. How about you just put the values in a variable and use the existing facility to put those variables in the prompt? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create if not exists (CINE)
On Wed, May 6, 2009 at 3:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: 1. Why should it do nothing if the object already exists (as opposed to any other alternative)? Answer: Because that's what CREATE IF NOT EXISTS means when interpreted as English. The argument was not about whether that is the plain meaning of the phrase; it was about whether that is a safe and useful behavior for a command to have. There is a pretty substantial group of people who think that it would be quite unsafe, which is why we failed to arrive at a consensus that this is a good thing to implement. I need this feature and have a good case. We (at over-blog/jfg-networks) use slony-1 for replication. When i create a new materialized view : 1) I create the materialized view with a create ... as select ... on the master node. 2) I create the needed functions and triggers. 3) I create a sql file including : the structure of the table, the functions, the triggers. I don't want to create the table on slave node using a create as select but using a simple good old create. (slony need an empty table when subscribing a new table to replication. Doing this create as select on slave node will stop the production for a looong time (as slony exclusively lock the whole set when executing thoses kind of scripts)) 4) the standard procedure is to do an execute script in slony to create all thoses thoses table/triggers/etc... 5) slony execute this script on the master and if it doesn't fail, it execute the scripts on all slaves nodes. But : if i do a simple CREATE, the execution fail on the master because the table already exist. I don't want to add a DROP IF EXISTS because it will drop the content of the materialized view on the master, and will replace it with an empty regular table. Solution : If i had a CREATE IF NOT EXISTS, the script won't fail on the master and will execute correctly on all nodes. Thank you -- Laurent Laborde Sysadmin at jfg://networks -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Kevin Grittner wrote: While discussing potential changes to PostgreSQL documentation of transaction isolation levels, Emmanuel Cecchet pointed out an intriguing new paper[1] on a new algorithm to provide true serializable behavior in a MVCC based database, with no additional blocking; although, there being no such things as a free lunch, there is an increase in serialization failures under contention. I have read through the paper and will share my comments. I hope I got it right: To put it in a nutshell, the approach to true serializability presented in the paper involves intention locks which do not block writers, but cause transactions with conflict potential to be aborted. The main cost incurred is the maintenance of these intention locks, which need to be kept for a while even after a transaction has committed. Moreover, there will potentially be many of these locks (think of SELECT COUNT(*) FROM ...), so some lock escalation mechanism (to page or table locks) will be necessary. I don't know how hard that would be to implement, but I'd argue that it is only worth considering if it guarantees true serializability. The paper describes only intention locks for rows in the select list of a statement and deliberately ignores rows which are examined in the WHERE clause. The authors argue in section 3.4 that this is no problem in their implementation since Berkeley DB does all locking and versioning on page granularity. I don't buy that; maybe I misunderstood something. Consider a function makehighlander(personid integer) RETURNS void defined like this: SELECT ishighlander INTO b FROM scots WHERE id=personid; IF b THEN RETURN; /* no need to do anything */ END IF; UPDATE scots SET ishighlander=TRUE WHERE id=personid; SELECT count(*) INTO n FROM scots WHERE ishighlander; IF (n 1) THEN RAISE EXCEPTION 'There can be only one'; END IF; If we assume that ishighlander is false for all records in the beginning, and there are two calls to the function with two personid's of records *in different pages*, then there cannot be any conflicts since all (write and intention) locks taken by each of these calls should only affect the one page that contains the one record that is updated and then found in the subsequent SELECT. Yet if the two execute concurrently and the two first SELECTs are executed before the two UPDATEs, then both functions have a snapshot so that the final SELECT statements will return 1 and both functions will succeed, leaving the table with two highlanders. So I think one would have to add intention locks for rows considered in the WHERE clause to guarantee true serializability. It would be interesting to know how many lines of code would have to be added to implement that and how performance would be affected. I'm afraid that concurrency would suffer because more conflicting transactions would be aborted. Another thing I wonder is whether the authors have considered the possibility that there are serializable and cursor stability transactions at the same time, where the latter would not take intention locks. Could that affect the considerations about correctness of the algorithm? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ECPG, two varchars with same name on same line
On Fri, May 01, 2009 at 03:49:47PM +0300, Heikki Linnakangas wrote: ECPG constructs internal struct names for VARCHAR fields using the field name and line number it's defined on. In a contrived example, though, that's not unique. Consider the following example: ... That hardly happens in practice, of course, but it's trivial to fix by just adding some more salt to the struct name, like a simple counter, so it seems we should. In principle you're right. However, the change needs to be added in several places like the internal variable structure that keeps the lineno anyway but needs to add the counter too. BTW we can remove the lineno then I think. Anyway, given that we are close to a release and the bug apparently never got up in a real life usage for years I'd prefer to not change it now but wait until the release has been done. Comments anyone? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use PostgreSQL! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Albe Laurenz laurenz.a...@wien.gv.at writes: So I think one would have to add intention locks for rows considered in the WHERE clause to guarantee true serializability. Does the paper explain how to deal with rows considered in the WHERE clause which don't yet exist? Ie, SELECT count(*) WHERE foo needs to take out a lock which would cause any transaction which inserts a new record where foo is true to be abort. In MSSQL this requires locking the page of the index where such records would be inserted (or the entire table if there's no index). In Predicate locking schemes this requires a separate storage structure for storing such predicates which can be arbitrarily complex expressions to check any new tuple being inserted against. Are these intention locks predicate locks, in that they're not associated with actual pages or records but with potential records which might be inserted in the future? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] conditional dropping of columns/constraints
Hi Tom, hi all,
On 05/06/2009 11:43 PM, Tom Lane wrote:
Andres Freundand...@anarazel.de writes:
As this is my first patch to PG I am happy with most sort of feedback.
Please add your patch to the commit-fest queue here:
http://wiki.postgresql.org/wiki/CommitFestInProgress
Will do so, after this email has arrived.
Since we are still busy with 8.4 beta, it's unlikely that anyone will
take a close look until the next commit fest begins. FWIW, I took a
very fast look through the patch and thought it was at least touching
the right places, except I think you missed equalfuncs.c. (It'd be a
good idea to grep for all uses of AlterTableCmd struct to see if you
missed anything else.) I don't have time now to look closer or do any
testing.
Ok, fixed that place (stupid me, I had seen that it is used there and
forgot about it) and found no other places.
Thanks,
Andres
From adca6b0f0409c3ea95c5e93ab5e1d8f3f3edf802 Mon Sep 17 00:00:00 2001
From: Andres Freund and...@anarazel.de
Date: Thu, 7 May 2009 00:53:45 +0200
Subject: [PATCH] Feature: DROP (COLUMN|CONSTRAINT) IF EXISTS
---
doc/src/sgml/ref/alter_table.sgml | 14 --
src/backend/commands/tablecmds.c | 74 -
src/backend/nodes/copyfuncs.c |1 +
src/backend/nodes/equalfuncs.c|1 +
src/backend/parser/gram.y | 22 +
src/include/nodes/parsenodes.h|1 +
src/test/regress/expected/alter_table.out | 10
src/test/regress/sql/alter_table.sql |9
8 files changed, 106 insertions(+), 26 deletions(-)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index fe3f388..9678236 100644
*** a/doc/src/sgml/ref/alter_table.sgml
--- b/doc/src/sgml/ref/alter_table.sgml
*** ALTER TABLE replaceable class=PARAMETE
*** 33,39
where replaceable class=PARAMETERaction/replaceable is one of:
ADD [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable replaceable class=PARAMETERtype/replaceable [ replaceable class=PARAMETERcolumn_constraint/replaceable [ ... ] ]
! DROP [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [ SET DATA ] TYPE replaceable class=PARAMETERtype/replaceable [ USING replaceable class=PARAMETERexpression/replaceable ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET DEFAULT replaceable class=PARAMETERexpression/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable DROP DEFAULT
--- 33,39
where replaceable class=PARAMETERaction/replaceable is one of:
ADD [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable replaceable class=PARAMETERtype/replaceable [ replaceable class=PARAMETERcolumn_constraint/replaceable [ ... ] ]
! DROP [ COLUMN ] [ IF EXISTS ] replaceable class=PARAMETERcolumn/replaceable [ RESTRICT | CASCADE ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable [ SET DATA ] TYPE replaceable class=PARAMETERtype/replaceable [ USING replaceable class=PARAMETERexpression/replaceable ]
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET DEFAULT replaceable class=PARAMETERexpression/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable DROP DEFAULT
*** where replaceable class=PARAMETERact
*** 41,47
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STATISTICS replaceable class=PARAMETERinteger/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD replaceable class=PARAMETERtable_constraint/replaceable
! DROP CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE REPLICA TRIGGER replaceable class=PARAMETERtrigger_name/replaceable
--- 41,47
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STATISTICS replaceable class=PARAMETERinteger/replaceable
ALTER [ COLUMN ] replaceable class=PARAMETERcolumn/replaceable SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
ADD replaceable class=PARAMETERtable_constraint/replaceable
! DROP CONSTRAINT [ IF EXISTS ] replaceable class=PARAMETERconstraint_name/replaceable [ RESTRICT | CASCADE ]
DISABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE TRIGGER [ replaceable class=PARAMETERtrigger_name/replaceable | ALL | USER ]
ENABLE REPLICA TRIGGER replaceable class=PARAMETERtrigger_name/replaceable
*** where replaceable class=PARAMETERact
*** 82,88
/varlistentry
varlistentry
! termliteralDROP COLUMN/literal/term
Re: [HACKERS] ECPG, two varchars with same name on same line
Michael Meskes wrote: On Fri, May 01, 2009 at 03:49:47PM +0300, Heikki Linnakangas wrote: ECPG constructs internal struct names for VARCHAR fields using the field name and line number it's defined on. In a contrived example, though, that's not unique. Consider the following example: ... That hardly happens in practice, of course, but it's trivial to fix by just adding some more salt to the struct name, like a simple counter, so it seems we should. In principle you're right. However, the change needs to be added in several places like the internal variable structure that keeps the lineno anyway but needs to add the counter too. BTW we can remove the lineno then I think. Anyway, given that we are close to a release and the bug apparently never got up in a real life usage for years I'd prefer to not change it now but wait until the release has been done. Yeah, if the fix isn't trivial, it's not worth it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Tue, 2009-05-05 at 10:50 -0500, Kevin Grittner wrote: This paper describes a modification to the concurrency control algorithm of a database management system that automatically detects and prevents snapshot isolation anomalies at runtime for arbitrary applications, thus providing serializable isolation. The new algorithm preserves the properties that make snapshot isolation attractive, including that readers do not block writers and vice versa. An implementation and performance study of the algorithm are described, showing that the throughput approaches that of snapshot isolation in most cases. I think this is important work in database theory and a good future direction for us. It's the right thing to keep an eye on developments and to consider implementation. We would need to decide whether intention locks were indeed necessary when we have MVCC also. Other DBMS without visibility may need to be more restrictive than we would have to be to implement this. Not sure. It wouldn't be 692 lines of code and even if it were the impact of that code would be such that it would need to be optional, since it would differ in definition from an existing SQL Standard isolation mode and it would have additional performance implications. If the use is optional, I would currently prefer the existing mechanism for implementing serialization, which is to serialize access directly using either a LOCK statement or an exclusive advisory lock. It's clear that any new-theory solution will cost significantly more as the number of users increases, at least O(N^2), whereas simply waiting is only O(N), AFAICS. (Michael et al don't discuss the algorithmic complexity). So it seems its use would require some thought and care and possibly further research to uncover areas of applicability in real usage. So for me, I would say we leave this be until the SQLStandard changes to recognise the additional mode. I don't see much advantage for us in breaking the ground on this feature and it will be costly to implement, so is a good PhD project. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP patch for TODO Item: Add prompt escape to display the client and server versions
Em Qui, 2009-05-07 às 10:11 +0300, Peter Eisentraut escreveu: On Thursday 07 May 2009 05:23:41 Dickson S. Guedes wrote: This is a WIP patch (for the TODO item in the subject) that I'm putting in the Commit Fest queue for 8.5. How about you just put the values in a variable and use the existing facility to put those variables in the prompt? Change all pset.version calculations in case 'V' to something like case 'v' is doing with PG_VERSION? Yes, could be better. The specific code used to %V and %v in this patch was inspired in the code in connection_warnings function in commands.c, so should this be refactored too? Thanks. Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://planeta.postgresql.org.br signature.asc Description: Esta é uma parte de mensagem assinada digitalmente
Re: [HACKERS] Serializable Isolation without blocking
Simon Riggs wrote: It wouldn't be 692 lines of code and even if it were the impact of that code would be such that it would need to be optional, since it would differ in definition from an existing SQL Standard isolation mode and it would have additional performance implications. I thought it would be equal to the SQL standard Serializable mode, whereas what we currently call serializable is in fact not as strong as the SQL standard Serializable mode. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, 2009-05-07 at 15:26 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: It wouldn't be 692 lines of code and even if it were the impact of that code would be such that it would need to be optional, since it would differ in definition from an existing SQL Standard isolation mode and it would have additional performance implications. I thought it would be equal to the SQL standard Serializable mode, whereas what we currently call serializable is in fact not as strong as the SQL standard Serializable mode. Our serializable is the same as Oracle's implementation. I think it would be confusing and non-useful to redefine ours, when it has already been accepted that the Oracle definition implements the standard reasonably closely. If that changes, we should also, however. Perhaps the key point is the O(N^2) complexity of the new algorithm. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
On Thu, 2009-05-07 at 12:15 +0300, Heikki Linnakangas wrote: Yeah, I think you're right. If you omit pg_xlog from the base backup, as we recommend in the manual, and clear the old files from the archive too, then you won't have the old history file around. Sorry about this, but I don't agree with that fix and think it needs more discussion, at very least. (I'm also not sure why this fix needs to applied with such haste, even taking priority over other unapplied patches.) The error seems to come from deleting the history file from the archive, rather than from the sequence of actions. A more useful thing might be to do an xlog switch before we do the shutdown checkpoint at end of recovery. That gives the same sequence of actions without modifying the existing sequence of activities for backups, which is delicate enough for me to not want to touch it. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Greg Stark wrote: So I think one would have to add intention locks for rows considered in the WHERE clause to guarantee true serializability. Does the paper explain how to deal with rows considered in the WHERE clause which don't yet exist? Ie, SELECT count(*) WHERE foo needs to take out a lock which would cause any transaction which inserts a new record where foo is true to be abort. Quote: To prevent phantoms in a system with row-level locking and versioning, the algorithm described here would need to be extended to take SIREAD locks on larger granules analogously to multigranularity intention locks in traditional two-phase locking systems. [...] We have not pursued the details in this paper because the phantom issue does not arise in our prototype implementation, since Oracle Berkeley DB does all locking and versioning at page granularity. End quote. Are these intention locks predicate locks, in that they're not associated with actual pages or records but with potential records which might be inserted in the future? No, they are associated with the page that contains the actual record. I think that's also meant with the larger granules in the above quote: Take an intention lock on every page which might affect the condition. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extra cost of lossy mode Bitmap Scan plan
higepon hige...@gmail.com writes: But I don't understand this case. select * from emp where emp_no 1; Is Bitmap Scan is faster than Index Scan in this case ? Yes, very probably, if a lot of tuples are being retrieved. A bitmap scan will fetch the tuples from the heap in a more or less optimal fashion --- for instance, each page is read only once. Index scan will result in a random sequence of accesses to the heap. (Of course, it might have some order if the index is well correlated with the heap order, but most of the time that's not true.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] create if not exists (CINE)
On Wed, May 6, 2009 at 9:04 AM, Dawid Kuroczko qne...@gmail.com wrote: On Wed, May 6, 2009 at 7:22 AM, Asko Oja asc...@gmail.com wrote: It was just yesterday when i wondering why we don't have this feature (i was trying to use it and it wasn't there :). The group of people who think it's unsafe should not use the feature. Clearly this feature would be useful when managing large amounts of servers and would simplify our release process. On Wed, May 6, 2009 at 5:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: [...] Yes, I did. I'm not any more convinced than I was before. In particular, the example you give is handled reasonably well without *any* new features, if one merely ignores object already exists errors. It sounds pretty amazing. Ignoring errors as a suggested way to use PostgreSQL. We run our release scripts inside transactions (with exception of concurrent index creation). So if something unexpected happens we are left still in working state. PostgreSQL ability to do DDL changes inside transaction was one of biggest surprises/improvements when switching from Oracle. Now you try to bring us down back to the level of Oracle :) Hm, You can do it easily today with help of PL/PgSQL, say like this: CREATE OR REPLACE FUNCTION foo_upgrade() RETURNS VOID AS $$ BEGIN BEGIN CREATE TABLE foo(i int, t text); EXCEPTION WHEN duplicate_table THEN RAISE NOTICE 'Table foo already exists'; That's kinda like, when parallel parking, determining that it's time to pull forward when you hit the car behind you. If you are going through the trouble of making a function to do schema upgrades, you would definitely want to query the information schema first to determine if you needed to create table, add columns, etc. Subtransactions should be used to handled _unexpected_ errors. Personally I don't like 'CREATE IF NOT EXISTS'. I find it 'messy'. :-) What I wish PostgreSQL would have is ability to do conditional rollback to savepoint. This way one could write a PostgreSQL SQL script that would contain conditional behaviour similar to exceptions handling above. For instance backend could handle sort of EXCEPTION clause: I've griped endlessly about this...I think the 'savepoint' command is worthless without additional functionality. In the early drafts of subtransactions, this wasn't the case...you could push and pop transactions without using plpgsql. I don't know how to fix the current behavior though...maybe: begin; savepoint x; stuff recover; commit; Where recover rolls back to last substransaction if there's an error else its a NOP. (this idea may have already failed to passed muster...i've floated several ideas over the years). With proper subtransaction support in sql, $SUBJECT wouldn't be necessary, because we could use the car-smack method (you could make the same case for drop..if exists which we already have). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Albe Laurenz laurenz.a...@wien.gv.at wrote: maybe I misunderstood something. Consider a function makehighlander(personid integer) RETURNS void defined like this: SELECT ishighlander INTO b FROM scots WHERE id=personid; IF b THEN RETURN; /* no need to do anything */ END IF; UPDATE scots SET ishighlander=TRUE WHERE id=personid; SELECT count(*) INTO n FROM scots WHERE ishighlander; IF (n 1) THEN RAISE EXCEPTION 'There can be only one'; END IF; If we assume that ishighlander is false for all records in the beginning, and there are two calls to the function with two personid's of records *in different pages*, then there cannot be any conflicts since all (write and intention) locks taken by each of these calls should only affect the one page that contains the one record that is updated and then found in the subsequent SELECT. Yet if the two execute concurrently and the two first SELECTs are executed before the two UPDATEs, then both functions have a snapshot so that the final SELECT statements will return 1 and both functions will succeed, leaving the table with two highlanders. I do think you misunderstood. If there are two concurrent executions and each reads one row, there will be an SIREAD lock for each of those rows. As an example, let's say that one of them (T0) updates its row and does its count, finds everything looks fine, and commits. In reading the row the other transaction (T1) modified it sets the T0.outConflict flag to true and the T1.inConflict flag to true. No blocking occurs. Now T1 updates its row. Still no problem, because if it committed there, there would still be a sequence of transactions (T0 followed by T1) which would be consistent with the results; but it selects rows which include the one modified by T0, which causes T0.inConflict and T1.outConflict to be set to true. These would both be pivots in an unsafe pattern of updates. No mystery which one needs to be rolled back -- T0 has already committed; so T1 is rolled back with a serialization failure (probably indicating that it is an unsafe update versus an update conflict or a deadlock, which are two other forms of serialization failure). Assuming that the software recognizes the serialization failure code and retries, it now finds that there is already a highlander and fails for real. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Gregory Stark st...@enterprisedb.com wrote: Albe Laurenz laurenz.a...@wien.gv.at writes: So I think one would have to add intention locks for rows considered in the WHERE clause to guarantee true serializability. Does the paper explain how to deal with rows considered in the WHERE clause which don't yet exist? Ie, SELECT count(*) WHERE foo needs to take out a lock which would cause any transaction which inserts a new record where foo is true to be abort. The issue is mentioned, along with the note, quoted in my original post, of why they were able to dodge the issue in the Berkeley DB implementation. In MSSQL this requires locking the page of the index where such records would be inserted (or the entire table if there's no index). This is the only form of predicate locking I've seen in real-world production databases which provide true serializable behavior. In Predicate locking schemes this requires a separate storage structure for storing such predicates which can be arbitrarily complex expressions to check any new tuple being inserted against. I've never seen that done in real-world production databases, although I'm sure it's pretty in theory. Are these intention locks predicate locks, in that they're not associated with actual pages or records but with potential records which might be inserted in the future? They are predicate locks in the sense that they detect all conflicts which could occur based on the actual predicate, though they tend to indicate conflicts in some situations where a rigorous (and expensive) analisys of the actual predicates might not; but please note that such locks would be SIREAD locks, which don't block any data modification, but are only used to detect dangerous update patterns. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Wed, 2009-05-06 at 15:18 -0400, Tom Lane wrote: Dickson S. Guedes lis...@guedesoft.net writes: Em Qua, 2009-05-06 s 09:37 -0400, Tom Lane escreveu: Seems like the right policy for that is run pgbench in its own database. A text warning about this could be shown at start of pgbench if the target database isn't named pgbench, for examplo, or just some text could be added to the docs. There already is a prominent warning in the pgbench docs: Caution pgbench -i creates four tables accounts, branches, history, and tellers, destroying any existing tables of these names. Be very careful to use another database if you have tables having these names! Holy Handgrenade, what a huge footgun! It doesn't even have a conceivable upside. The table names accounts and history are fairly common and a caution isn't a sufficient safeguard on production data. We know the manual rarely gets read *after* a problem, let alone beforehand. We should check they are the correct tables before we just drop them. Perhaps check for the comment Tables for pgbench application. Not production data on the tables, which would be nice to add anyway. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Kevin Grittner wrote: maybe I misunderstood something. Consider a function makehighlander(personid integer) RETURNS void defined like this: SELECT ishighlander INTO b FROM scots WHERE id=personid; IF b THEN RETURN; /* no need to do anything */ END IF; UPDATE scots SET ishighlander=TRUE WHERE id=personid; SELECT count(*) INTO n FROM scots WHERE ishighlander; IF (n 1) THEN RAISE EXCEPTION 'There can be only one'; END IF; If we assume that ishighlander is false for all records in the beginning, and there are two calls to the function with two personid's of records *in different pages*, then there cannot be any conflicts since all (write and intention) locks taken by each of these calls should only affect the one page that contains the one record that is updated and then found in the subsequent SELECT. Yet if the two execute concurrently and the two first SELECTs are executed before the two UPDATEs, then both functions have a snapshot so that the final SELECT statements will return 1 and both functions will succeed, leaving the table with two highlanders. I do think you misunderstood. If there are two concurrent executions and each reads one row, there will be an SIREAD lock for each of those rows. As an example, let's say that one of them (T0) updates its row and does its count, finds everything looks fine, and commits. In reading the row the other transaction (T1) modified it sets the T0.outConflict flag to true and the T1.inConflict flag to true. Where does T0 read the row that T1 modified? No blocking occurs. Now T1 updates its row. Wait a minute, I am confused. I thought T1 had already modified the row before T0 committed? Or is modify not the update? Still no problem, because if it committed there, there would still be a sequence of transactions (T0 followed by T1) which would be consistent with the results; but it selects rows which include the one modified by T0, which causes T0.inConflict and T1.outConflict to be set to true. Where does T1 select rows that were modified by T0? It selects only one row, the one it modified itself, right? These would both be pivots in an unsafe pattern of updates. No mystery which one needs to be rolled back -- T0 has already committed; so T1 is rolled back with a serialization failure (probably indicating that it is an unsafe update versus an update conflict or a deadlock, which are two other forms of serialization failure). Assuming that the software recognizes the serialization failure code and retries, it now finds that there is already a highlander and fails for real. You see, there must be something fundamental I am getting wrong. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Albe Laurenz laurenz.a...@wien.gv.at wrote: Kevin Grittner wrote: I do think you misunderstood. If there are two concurrent executions and each reads one row, there will be an SIREAD lock for each of those rows. As an example, let's say that one of them (T0) updates its row and does its count, finds everything looks fine, and commits. In reading the row the other transaction (T1) modified it sets the T0.outConflict flag to true and the T1.inConflict flag to true. Where does T0 read the row that T1 modified? As I said in the original post, I think we would need to track SIREAD locks in the structures which back the pg_locks view. blocking occurs. Now T1 updates its row. Wait a minute, I am confused. I thought T1 had already modified the row before T0 committed? Or is modify not the update? There are so many sequences that I didn't think it was worthwhile to step through them all, I did say As an example, let's say that one of them (T0) updates its row and does its count, finds everything looks fine, and commits. If you want to work through the case where they both UPDATE their rows before either commits, OK; it's not that different. Things are OK as far as the first select of a modified row by the other transaction; you record inConflict for one and outConflict for the other. At the point where it goes both directions, it is clear that there is a dangerous interaction and one or the other is rolled back. Where does T1 select rows that were modified by T0? It selects only one row, the one it modified itself, right? You have to select it to know whether to count it, right? You see, there must be something fundamental I am getting wrong. It is such a radical departure from traditional blocking approaches, that it can be hard to get your head around it. :) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon Riggs wrote: It wouldn't be 692 lines of code and even if it were the impact of that code would be such that it would need to be optional, since it would differ in definition from an existing SQL Standard isolation mode and it would have additional performance implications. I thought it would be equal to the SQL standard Serializable mode, whereas what we currently call serializable is in fact not as strong as the SQL standard Serializable mode. Exactly. The standard probably *should* add SNAPSHOT between REPEATABLE READ and SERIALIZABLE, but so far have not. As of the 2003 version of the SQL spec, they added explicit language that makes it clear that what you get when you ask for SERIALIZABLE mode in PostgreSQL is *not* compliant (although it is more than adequate for REPEATABLE READ). By the way, the other modes are all optional, as you're allowed to escalate to a higher level whenever a lower level is requested; SERIALIZABLE is required by the standard and is specified as the default. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Kevin Grittner wrote: Where does T1 select rows that were modified by T0? It selects only one row, the one it modified itself, right? You have to select it to know whether to count it, right? We are getting closer. So an SIREAD lock is taken for every row that is examined during the execution of an execution plan? Ah. What if there is an index on the ishighlander row? Then an index scan would find only one candidate to examine, and the other rows would not even be touched by the execution plan. Then how would they contract an SIREAD lock? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [BUGS] BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
Simon Riggs wrote: On Thu, 2009-05-07 at 12:15 +0300, Heikki Linnakangas wrote: Yeah, I think you're right. If you omit pg_xlog from the base backup, as we recommend in the manual, and clear the old files from the archive too, then you won't have the old history file around. ... A more useful thing might be to do an xlog switch before we do the shutdown checkpoint at end of recovery. That gives the same sequence of actions without modifying the existing sequence of activities for backups, which is delicate enough for me to not want to touch it. Hmm, yeah should work as well. I find the recovery sequence to be even more delicate, though, than pg_start_backup(). I think you'd need to write the XLOG switch record using the old timeline ID, as we currently require that the timeline changes only at a shutdown checkpoint record. That's not hard, but does make me a bit nervous. The advantage of that over switching xlog segment in pg_start_backup() would be that you would go through fewer XLOG segments if you took backups often. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
On Thu, 2009-05-07 at 17:54 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: On Thu, 2009-05-07 at 12:15 +0300, Heikki Linnakangas wrote: Yeah, I think you're right. If you omit pg_xlog from the base backup, as we recommend in the manual, and clear the old files from the archive too, then you won't have the old history file around. ... A more useful thing might be to do an xlog switch before we do the shutdown checkpoint at end of recovery. That gives the same sequence of actions without modifying the existing sequence of activities for backups, which is delicate enough for me to not want to touch it. Hmm, yeah should work as well. I find the recovery sequence to be even more delicate, though, than pg_start_backup(). I think you'd need to write the XLOG switch record using the old timeline ID, as we currently require that the timeline changes only at a shutdown checkpoint record. That's not hard, but does make me a bit nervous. The advantage of that over switching xlog segment in pg_start_backup() would be that you would go through fewer XLOG segments if you took backups often. Yes, you're right about the delicacy of all of this so both suggestions sound kludgey - the problem is to do with timelines not with sequencing of checkpoints and log switches. The problem is Mikael deleted the history file and he shouldn't have done that. We need some explicit protection for when that occurs, I feel, to avoid it breaking again in the future with various changes we have planned. If the history file is so important, we shouldn't only store it in the archive. We should keep a copy locally as well and refer to it if the archived copy is missing. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Thu, May 7, 2009 at 10:12 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-05-06 at 15:18 -0400, Tom Lane wrote: Dickson S. Guedes lis...@guedesoft.net writes: Em Qua, 2009-05-06 s 09:37 -0400, Tom Lane escreveu: Seems like the right policy for that is run pgbench in its own database. A text warning about this could be shown at start of pgbench if the target database isn't named pgbench, for examplo, or just some text could be added to the docs. There already is a prominent warning in the pgbench docs: Caution pgbench -i creates four tables accounts, branches, history, and tellers, destroying any existing tables of these names. Be very careful to use another database if you have tables having these names! Holy Handgrenade, what a huge footgun! It doesn't even have a conceivable upside. The table names accounts and history are fairly common and a caution isn't a sufficient safeguard on production data. We know the manual rarely gets read *after* a problem, let alone beforehand. We should check they are the correct tables before we just drop them. Perhaps check for the comment Tables for pgbench application. Not production data on the tables, which would be nice to add anyway. I bet it would be just as good and a lot simpler to do what someone suggested upthread, namely s/^/pgbench_/ ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Albe Laurenz laurenz.a...@wien.gv.at wrote: What if there is an index on the ishighlander row? Then an index scan would find only one candidate to examine, and the other rows would not even be touched by the execution plan. I assume you're talking about this line of your function: SELECT count(*) INTO n FROM scots WHERE ishighlander; I'm further assuming that you meant an index on the ishighlander *column*. I can think of more than one way to handle that. Off the top of my head, I think it would work to acquire an update lock on both old and new index entries for a row when it is updated, and to lock the range of an index used for a scan with the new SIREAD lock. Or perhaps, since the row must be visited to test visibility, the update lock could be on the old and new rows, and the index scan would find the conflict in that way. Or it could keep track of the various tuples which represent different mutations of a row, and link back from the not visible to me row which has been updated to true, and find that it is a mutation of a visible row. These are important implementation details to be worked out (very carefully!). I don't claim to have worked through all such details yet, or even to be familiar enough with the PostgreSQL internals to do so in a reasonable time. :-( -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Thu, 2009-05-07 at 11:14 -0400, Robert Haas wrote: We should check they are the correct tables before we just drop them. Perhaps check for the comment Tables for pgbench application. Not production data on the tables, which would be nice to add anyway. I bet it would be just as good and a lot simpler to do what someone suggested upthread, namely s/^/pgbench_/ Running pgbench has become more popular now, with various people recommending running it on every system to test performance. I don't disagree with that recommendation, but I've had problems myself with a similar issue - hence earlier patch to prevent pgbench running a complete database VACUUM before every test run. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
Simon Riggs wrote: On Thu, 2009-05-07 at 17:54 +0300, Heikki Linnakangas wrote: Simon Riggs wrote: A more useful thing might be to do an xlog switch before we do the shutdown checkpoint at end of recovery. That gives the same sequence of actions without modifying the existing sequence of activities for backups, which is delicate enough for me to not want to touch it. Hmm, yeah should work as well. I find the recovery sequence to be even more delicate, though, than pg_start_backup(). I think you'd need to write the XLOG switch record using the old timeline ID, as we currently require that the timeline changes only at a shutdown checkpoint record. That's not hard, but does make me a bit nervous. Yes, you're right about the delicacy of all of this so both suggestions sound kludgey - the problem is to do with timelines not with sequencing of checkpoints and log switches. The problem is Mikael deleted the history file and he shouldn't have done that. I don't see any user error here. What he did was: 1. Restore from backup A 2. Clear old WAL archive 3. pg_start_backup() + tar all but pg_xlog + pg_stop_backup(); 4. Restore new backup B There's no history file in the archive because it was cleared in step 2. There's nothing wrong with that; you only need to retain WAL files from the point that you call pg_start_backup(). There's no history file either in the tar, because pg_xlog was not tarred as we recommend in the manual. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Simon Riggs si...@2ndquadrant.com wrote: It wouldn't be 692 lines of code Agreed. The original implementation was in an MVCC database which already supported full serializability using strict 2 phase locking and used page level locks. Both of these made the implementation simpler than it would be in PostgreSQL. (And that's not even mentioning sub-transactions and distributed transactions!) and even if it were the impact of that code would be such that it would need to be optional I was thinking perhaps a GUC to allow traditional behavior when SERIALIZABLE is requested versus using snapshot isolation for REPEATABLE READ and this new technique for SERIALIZABLE. Would that be sane? If the use is optional, I would currently prefer the existing mechanism for implementing serialization, which is to serialize access directly using either a LOCK statement or an exclusive advisory lock. I'm sure many will, particularly where the number of tables is less than 100 and the number of queries which can be run concurrently is only a thousand or two. Picking out the potential conflicts and hand-coding serialization techniques becomes more feasible on a small scale like that. That said, there's a lot less room for mistakes here, once this new technique is implemented and settled in. When I was discussing the receipting and deposit scenario while trying to clarify the documentation of current behavior, I received several suggestions from respected members of this community for how that could be handled with existing techniques which didn't, in fact, correct the problem. That just points out to me how tricky it is to solve on an ad hoc basis, as opposed to a more rigorous technique like the one described in the paper. The only suggested fix which *did* work forced actual serialization of all receipts as well as actual serialization of those with the deposit report query. The beauty of this new technique is that there would not be any blocking in the described scenario, and there would be a rollback with serialization failure if (and only if) there was an attempt to run the deposit report query while a transaction for a receipt on the old date was still pending. I suspect that the concurrency improvements of the new technique over existing safe techniques would allow it to scale well, at least in our environment. It's clear that any new-theory solution will cost significantly more as the number of users increases, at least O(N^2), whereas simply waiting is only O(N), AFAICS. I'm not following your reasoning on the O(N^2). Could you explain why you think it would follow that curve? So it seems its use would require some thought and care and possibly further research to uncover areas of applicability in real usage. Care -- of course. Real usage for serializable transactions -- well known already. (Or are you just questioning performance here?) So for me, I would say we leave this be until the SQLStandard changes to recognise the additional mode. It already recognizes this mode; it doesn't yet recognize snapshot isolation (more's the pity). I don't see much advantage for us in breaking the ground on this feature and it will be costly to implement, so is a good PhD project. Apparently it's already been done as a PhD project -- by Michael Cahill, against InnoDB. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Some 8.4 changes needed according to pg_migrator testing
I was just talking to Bruce about his results from testing pg_migrator, and we realized there are a couple of changes that we need to slip into the core code before 8.4 goes final. 1. pg_dumpall dumps CREATE DATABASE commands that include the source database's encoding, lc_collate, and lc_ctype settings ... but if dumping from a pre-8.4 server it just omits the lc_ settings. This is flat-out wrong (independently of pg_migrator). The correct behavior when dumping from pre-8.4 is to get the server-wide locale settings and include those in the CREATE DATABASE commands. Otherwise you're not restoring the full state of the database correctly. This is critical in view of the 8.4 changes to make CREATE DATABASE enforce encoding-vs-locale match --- if you try to load the dump into a server with a different default locale, it'll probably fail, and there's absolutely no reason why it should. 2. There seem to be some corner cases where creating a table in the new database will not create a toast table even though there was one in the previous instance. (I'm not 100% convinced that this can happen if we create and then drop dropped columns, for instance ... but I'm not convinced it can't happen, either.) If there is a toast table in the old database then pg_migrator must bring it over because it might possibly contain live data. However, as toasting.c is presently coded there is no way to force it to create a toast table. I think we should change AlterTableCreateToastTable to add a bool force parameter. Alternatively we could add a separate entry point, but the option seems a bit cleaner. Barring objections I'll commit changes for both of these before beta2. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Please keep Michael Cahill copied on this thread, per his request. I just noticed the omission on a few messages and will forward them to him. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Tom Lane wrote: 2. There seem to be some corner cases where creating a table in the new database will not create a toast table even though there was one in the previous instance. (I'm not 100% convinced that this can happen if we create and then drop dropped columns, for instance ... but I'm not convinced it can't happen, either.) If there is a toast table in the old database then pg_migrator must bring it over because it might possibly contain live data. However, as toasting.c is presently coded there is no way to force it to create a toast table. I think we should change AlterTableCreateToastTable to add a bool force parameter. Alternatively we could add a separate entry point, but the option seems a bit cleaner. Hmm, what about toast reloptions? They are not a problem now of course, but could be in a 8.4-8.5 migration. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: 2. There seem to be some corner cases where creating a table in the new database will not create a toast table even though there was one in the previous instance. Hmm, what about toast reloptions? They are not a problem now of course, but could be in a 8.4-8.5 migration. I don't think it's an issue. The type of scenario we are looking at is where there is no need for a toast table *now*, but there might be some old rows hanging around that got toasted anyway. (Say, you originally had two wide varchar columns and then dropped one.) It seems unlikely that preserving the reloptions for the toast table is going to be all that critical in this type of scenario. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
* Robert Haas robertmh...@gmail.com [090507 11:15]: I bet it would be just as good and a lot simpler to do what someone suggested upthread, namely s/^/pgbench_/ That has the legacy compatibility problem... But seeing as legacy has a: SET search_path TO public; And uses plain table in it's queries/creates/drops, couldn't we just make new pgbench refer to tables as schema.table where schema is public? If we leave schema as public, and leave in the search_path, we should be identical to what we currently have, except we've explicliyt scoped was was searched for before. And it leads to an easy way for people to change public (in the search path and/or schema.table) to do other things (although I'm not saying that's necessarily required or desired either). a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Simon Riggs si...@2ndquadrant.com writes: On Thu, 2009-05-07 at 11:14 -0400, Robert Haas wrote: We should check they are the correct tables before we just drop them. Perhaps check for the comment Tables for pgbench application. Not production data on the tables, which would be nice to add anyway. I bet it would be just as good and a lot simpler to do what someone suggested upthread, namely s/^/pgbench_/ Running pgbench has become more popular now, with various people recommending running it on every system to test performance. I don't disagree with that recommendation, but I've had problems myself with a similar issue - hence earlier patch to prevent pgbench running a complete database VACUUM before every test run. Well, pgbench has been coded this way since forever and we've only seen this one report of trouble. Still, I can't object very hard to renaming the tables to pgbench_accounts etc --- it's a trivial change and the only thing it could break is custom pgbench scenarios that rely on the default scenario's tables, which there are probably not many of. So do we have consensus on dropping the SET search_path and renaming the tables? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Aidan Van Dyk ai...@highrise.ca writes: ... couldn't we just make new pgbench refer to tables as schema.table where schema is public? I'd prefer not to do that because it changes the amount of parsing work demanded by the benchmark. Maybe not by enough to matter ... or maybe it does. Adjusting the length of the identifiers is a small enough change that I'm prepared to believe it doesn't invalidate comparisons, but changing the set of catalog lookups that occur is another question. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Thu, 2009-05-07 at 12:47 -0400, Tom Lane wrote: Well, pgbench has been coded this way since forever and we've only seen this one report of trouble. Still, I can't object very hard to renaming the tables to pgbench_accounts etc --- it's a trivial change and the only thing it could break is custom pgbench scenarios that rely on the default scenario's tables, which there are probably not many of. So do we have consensus on dropping the SET search_path and renaming the tables? +1 (I hate prefixed table names but I get the idea) Joshua D. Drake regards, tom lane -- 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
* Tom Lane t...@sss.pgh.pa.us [090507 12:53]: Aidan Van Dyk ai...@highrise.ca writes: ... couldn't we just make new pgbench refer to tables as schema.table where schema is public? I'd prefer not to do that because it changes the amount of parsing work demanded by the benchmark. Maybe not by enough to matter ... or maybe it does. Adjusting the length of the identifiers is a small enough change that I'm prepared to believe it doesn't invalidate comparisons, but changing the set of catalog lookups that occur is another question. True enough... What about making the prefix be configurable, so by default, it could be pgbench_, it could be set to (to force it to use old pgbench names) or set to something. to get it to use a different schema (noting that the comparisons to older ones not doing catalog lookups are void). But by dropping the search_path, you're necessarily changing the catalog comparisons and lookups anyways, because your now taking a random search path to follow (which could have multiple entries in it) instead of one guaranteed to be a single, useable entry. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Thu, 2009-05-07 at 12:58 -0400, Aidan Van Dyk wrote: True enough... What about making the prefix be configurable, so by default, it could be pgbench_, it could be set to (to force it to use old pgbench names) or set to something. to get it to use a different schema (noting that the comparisons to older ones not doing catalog lookups are void). Then you have to pass the prefix on the command line. That seems a bit over doing it for such a simple utility. But by dropping the search_path, you're necessarily changing the catalog comparisons and lookups anyways, because your now taking a random search path to follow (which could have multiple entries in it) instead of one guaranteed to be a single, useable entry. Except that it isn't a guaranteed usable entry, which is why I submitted the patch. Sincerely, 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-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
* Joshua D. Drake j...@commandprompt.com [090507 13:02]: On Thu, 2009-05-07 at 12:58 -0400, Aidan Van Dyk wrote: True enough... What about making the prefix be configurable, so by default, it could be pgbench_, it could be set to (to force it to use old pgbench names) or set to something. to get it to use a different schema (noting that the comparisons to older ones not doing catalog lookups are void). Then you have to pass the prefix on the command line. That seems a bit over doing it for such a simple utility. Sure, but by putting a sane default (which seems to be leaning towards or pgbench_), you don't *need* to do anything on the command line. But by dropping the search_path, you're necessarily changing the catalog comparisons and lookups anyways, because your now taking a random search path to follow (which could have multiple entries in it) instead of one guaranteed to be a single, useable entry. Except that it isn't a guaranteed usable entry, which is why I submitted the patch. Well ya, but at least you didn't have any pgbench result to try and compare unevenly with something else ;-) a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Thu, 2009-05-07 at 09:53 -0700, Joshua D. Drake wrote: On Thu, 2009-05-07 at 12:47 -0400, Tom Lane wrote: Well, pgbench has been coded this way since forever and we've only seen this one report of trouble. Still, I can't object very hard to renaming the tables to pgbench_accounts etc --- it's a trivial change and the only thing it could break is custom pgbench scenarios that rely on the default scenario's tables, which there are probably not many of. So do we have consensus on dropping the SET search_path and renaming the tables? +1 (I hate prefixed table names but I get the idea) +1, sorry JD. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Joshua D. Drake j...@commandprompt.com writes: On Thu, 2009-05-07 at 12:58 -0400, Aidan Van Dyk wrote: True enough... What about making the prefix be configurable, so by default, it could be pgbench_, it could be set to (to force it to use old pgbench names) or set to something. to get it to use a different schema (noting that the comparisons to older ones not doing catalog lookups are void). Then you have to pass the prefix on the command line. That seems a bit over doing it for such a simple utility. Yes, this seems like vastly more work than is called for. But by dropping the search_path, you're necessarily changing the catalog comparisons and lookups anyways, because your now taking a random search path to follow (which could have multiple entries in it) instead of one guaranteed to be a single, useable entry. Except that it isn't a guaranteed usable entry, which is why I submitted the patch. I think this argument is bogus anyway. The tables are always going to be created in the default creation schema, ie, the first one on the search path. As long as you don't change the effective search_path between pgbench -i and the actual test runs, it won't matter whether that is public or something else. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, 2009-05-07 at 11:15 -0500, Kevin Grittner wrote: Please keep Michael Cahill copied on this thread, per his request. I just noticed the omission on a few messages and will forward them to him. Apologies Michael, I see that my mail did remove you. That was a unconscious error; I was particularly interested in your comments regarding my assessment of the algorithmic complexity of the new theory and existing serialization technique. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
I wrote: 1. pg_dumpall dumps CREATE DATABASE commands that include the source database's encoding, lc_collate, and lc_ctype settings ... but if dumping from a pre-8.4 server it just omits the lc_ settings. This is flat-out wrong (independently of pg_migrator). The correct behavior when dumping from pre-8.4 is to get the server-wide locale settings and include those in the CREATE DATABASE commands. Actually, there's another issue that comes to mind here: since we are relying on platform-dependent locale names, including those in the dump is going to pose a severe problem for porting dumps across platforms (where different platform could even mean different libc release). We're already at risk with respect to dumps from 8.4, even without the above-proposed change. I am not sure what we can do about this. Ideas? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
On Thu, 2009-05-07 at 18:57 +0300, Heikki Linnakangas wrote: I don't see any user error here. Just observing that the error occurs because we rely on a file being there when we haven't even documented that it needs to be there for it to work. File deletion with %r from the archive would not have removed that file at that point. We should have an explicit statement about which files can be deleted from the archive and which should not be, but in general it is dangerous to remove files that have not been explicitly described as removable. Playing with the order of events seems fragile and I would prefer a more explicit solution. Recording the timeline history permanently with each server would be a sensible and useful thing (IIRC DB2 does this). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, 2009-05-07 at 10:56 -0500, Kevin Grittner wrote: It's clear that any new-theory solution will cost significantly more as the number of users increases, at least O(N^2), whereas simply waiting is only O(N), AFAICS. I'm not following your reasoning on the O(N^2). Could you explain why you think it would follow that curve? Each user must compare against work performed by all other users. O(N). There are N users, so O(N^2). With reasonable tuning we can make that work with 10 users each checking the other's data, but with a 100 we'll end up spending more time checking for aborts (and aborting) than we would if we had just queued up for it. If you want this, the simplest implementation is to quite literally allow only a single SERIALIZABLE txn onto the system at any time. All other SERIALIZABLEs queue. Note that simple serialization requires no special handling for aborted transactions. Implementing that will be fast, proving it works is trivial and it seems will work better in the general case. Yeh, it sucks for medium arrival rate transactions, but its great for low or high arrival rate transactions. The new model is good for medium arrival rates only and will take a lot of work to implement, correctly and sufficiently optimally to keep the applicability window wide enough to justify the effort. Optimising it would basically entail implementing the equivalent of block-level locking. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Simon Riggs si...@2ndquadrant.com wrote: Each user must compare against work performed by all other users. O(N). There are N users, so O(N^2). Why does that apply here and not in the update conflict detection? -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [BUGS] BUG #4796: Recovery followed by backup creates unrecoverable WAL-file
Simon Riggs wrote: On Thu, 2009-05-07 at 18:57 +0300, Heikki Linnakangas wrote: I don't see any user error here. Just observing that the error occurs because we rely on a file being there when we haven't even documented that it needs to be there for it to work. File deletion with %r from the archive would not have removed that file at that point. We should have an explicit statement about which files can be deleted from the archive and which should not be, but in general it is dangerous to remove files that have not been explicitly described as removable. When you create a new base backup, you shouldn't need any files archived before starting the backup. You might not even have had archiving enabled before that, or you might change archive_command to archive into a new location before tarting the backup. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
On May 7, 2009, at 10:18 AM, Tom Lane wrote: Actually, there's another issue that comes to mind here: since we are relying on platform-dependent locale names, including those in the dump is going to pose a severe problem for porting dumps across platforms (where different platform could even mean different libc release). We're already at risk with respect to dumps from 8.4, even without the above-proposed change. I am not sure what we can do about this. Ideas? Abandon platform-dependent locales? Kidding! (Sort of.) Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Tom Lane wrote: Actually, there's another issue that comes to mind here: since we are relying on platform-dependent locale names, including those in the dump is going to pose a severe problem for porting dumps across platforms (where different platform could even mean different libc release). We're already at risk with respect to dumps from 8.4, even without the above-proposed change. I am not sure what we can do about this. Ideas? I don't think there's much we can do apart from telling the user not to move stuff across platforms that do not have equally named locales. Maybe what we can do is have a mechanism for pg_restore to map one locale from the dump file into another. So the user can specify a file with lines like en_US := English_UnitedStates etc (For text dumps, the only solution would be for the user to edit the dump manually; perhaps provide a pg_dump switch to avoid dumping locale config?). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Alvaro Herrera wrote: Tom Lane wrote: Actually, there's another issue that comes to mind here: since we are relying on platform-dependent locale names, including those in the dump is going to pose a severe problem for porting dumps across platforms (where different platform could even mean different libc release). We're already at risk with respect to dumps from 8.4, even without the above-proposed change. I am not sure what we can do about this. Ideas? I don't think there's much we can do apart from telling the user not to move stuff across platforms that do not have equally named locales. Maybe what we can do is have a mechanism for pg_restore to map one locale from the dump file into another. So the user can specify a file with lines like en_US := English_UnitedStates etc (For text dumps, the only solution would be for the user to edit the dump manually; perhaps provide a pg_dump switch to avoid dumping locale config?). We have a pg_dump switch that sets the encoding. Perhaps we could have a pg_dump switch that fakes the output locale? Seems awfully kludgy though - I'd much rather see us supporting it on pg_restore and just say that if you are dumping in plaintext, well, use a plaintext editor to edit it. //Magnus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Outer join bug in CVS HEAD
I just bumped into this: postgres=# CREATE TABLE atable (id int4); CREATE TABLE postgres=# CREATE TABLE btable (id int4); CREATE TABLE postgres=# INSERT INTO atable VALUES (1),(2),(3); INSERT 0 3 postgres=# INSERT INTO btable VALUES (1),(2),(3),(1); INSERT 0 4 postgres=# SELECT * FROM atable WHERE id IN (SELECT d.id FROM atable d LEFT JOIN btable e ON d.id = e.id) ; id 1 1 2 3 (4 rows) On 8.3 this returns correctly just three rows: 1 2 3. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Outer join bug in CVS HEAD
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: I just bumped into this: [ scratches head ... ] It seems to be reordering the two joins, which it's not supposed to do. Will look. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Magnus Hagander mag...@hagander.net writes: Alvaro Herrera wrote: (For text dumps, the only solution would be for the user to edit the dump manually; perhaps provide a pg_dump switch to avoid dumping locale config?). We have a pg_dump switch that sets the encoding. Perhaps we could have a pg_dump switch that fakes the output locale? Seems awfully kludgy though - I'd much rather see us supporting it on pg_restore and just say that if you are dumping in plaintext, well, use a plaintext editor to edit it. I don't think a solution that requires you to know about this in advance (ie when you make the dump) is going to be very satisfactory. I'm inclined to think that the most usable answer is to have some way of getting CREATE DATABASE itself to apply a locale-name mapping. Or we could try to make the user-visible locale names platform-independent in the first place, a la David's not-silly-at-all suggestion. I think the part that goes en_US or whatever is actually quite well standardized (except for good ol' Windows, but we could provide a mapping from the Unix-style names to Windows names). It's the encoding-name part that's not very stable. If we could hide that from the user and tack it on internally, things would be much better. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, 2009-05-07 at 12:39 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: Each user must compare against work performed by all other users. O(N). There are N users, so O(N^2). Why does that apply here and not in the update conflict detection? I think the shoe is on the other foot. :-) Explain what you think the algorithmic complexity is, and why, if that's not correct. Can you beat O(N), with Postgres? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2009-05-07 at 12:39 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: Each user must compare against work performed by all other users. O(N). There are N users, so O(N^2). Why does that apply here and not in the update conflict detection? I think the shoe is on the other foot. :-) That's a question, and I think a fair one. As with update conflict detection, you check whether there are any conflicting locks for what you are currently accessing. For most usage patterns you won't find conflicting access the vast majority of the time. The assertion that there is some need for each session to wade through something for every other session seems baseless to me. I'm wondering what I might be missing. If you throw a formula out there, I do think it's incumbent on you to explain why you think it fits. If I threw a formula out there, then it would be fair of you to ask me to explain how I got to it. I'm not at a point where I think I can estimate performance impact. I guess I would tend to start from the benchmarks published in the paper, some of which were confirmed by the ACM SIGMOD repeatability committee. Eyeballing that, it looks to me like the worst case they found was about a 15% performance hit, with large stretches of some of the graphs hanging within 1% of the performance of straight snapshot isolation. I think that given published benchmarks with confirmation from an independent organization like ACM, it would be incumbent on anyone who questions the benchmarks to explain why they think they're not accurate or useful. The only concern I've seen so far has been that these benchmarks lack long and complex database transactions, which seems like a fair concern. Scalability with additional concurrent sessions seems good as far as they took it, which was 50 sessions. Even on a server with 16 CPUs backing a database with 3 million to 4 million hit per day, with tens of millions of database transactions per day, we use a connection pool with fewer sessions than that. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, 2009-05-07 at 15:10 -0500, Kevin Grittner wrote: The assertion that there is some need for each session to wade through something for every other session seems baseless to me. I'm wondering what I might be missing. That's Greg's point. Do we need full locking of everything we might touch, or tracking of what we have touched? That question is still unanswered. If you need the might touch then you either need to implement locking that will effect everybody (which ain't ever gonna fly round here), or you implement a scheme that is harder work but avoids locking. That is clearly O(N^2) for non-locking design. If you track have touched only then we can do that with a hash table in shared memory. That would be O(k), if it is possible. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
--On 7. Mai 2009 15:32:01 -0400 Tom Lane t...@sss.pgh.pa.us wrote: I think the part that goes en_US or whatever is actually quite well standardized (except for good ol' Windows, but we could provide a mapping from the Unix-style names to Windows names). I like this idea, but i could imagine that this is pretty hard to maintain, once someone decides to change things suddenly? -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Bernd Helmle maili...@oopsware.de writes: --On 7. Mai 2009 15:32:01 -0400 Tom Lane t...@sss.pgh.pa.us wrote: I think the part that goes en_US or whatever is actually quite well standardized (except for good ol' Windows, but we could provide a mapping from the Unix-style names to Windows names). I like this idea, but i could imagine that this is pretty hard to maintain, once someone decides to change things suddenly? Well, we'd probably want to make sure there was an escape-hatch whereby you could specify an exact platform-dependent locale name, in case whatever we were doing didn't work on a particular platform. I just don't want that to be the norm. Possibly it would work to first try the locale name as given by the user, and if that doesn't work (either isn't recognized, or doesn't seem to use the right encoding) then try to map/modify it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, May 7, 2009 at 6:31 PM, Simon Riggs si...@2ndquadrant.com wrote: Each user must compare against work performed by all other users. O(N). There are N users, so O(N^2). i think this logic only works if you must scan every item for every other user every time. If you have data structures like binary trees or whatever to fine any matching predicate locks or intent locks or whatever we're calling them then you can hopefully find them in faster than O(N) time. I'm not sure you can do better than a full linear search though. If I do something like SELECT count(*) FROM tab WHERE complex_function(a,b) = 5 And then you INSERT INTO tab (a,b) VALUES (1,2). How would you store any record of the fact that there's a serialization failure iff complex_function(1,2)=5 in any way that lets you look it up in any way other than evaluating complex_function for every set of values inserted? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Simon Riggs si...@2ndquadrant.com wrote: Do we need full locking of everything we might touch, or tracking of what we have touched? If you need the might touch then you either need to implement locking that will effect everybody (which ain't ever gonna fly round here), or you implement a scheme that is harder work but avoids locking. That is clearly O(N^2) for non-locking design. If you track have touched only then we can do that with a hash table in shared memory. That would be O(k), if it is possible. To quote what I think is a relevant section from the paper: One property of Berkeley DB that simplified our implementation was working with page level locking and versioning. In databases that version and lock at row-level granularity (or finer), additional effort would be required to avoid phantoms, analogous to standard two phase locking approaches such as multigranularity locking. Since these techniques are used in quite a few databases, I assume that implementation is fairly well understood. The big difference is that rather than traditional read locks which block updates, it would be these new non-blocking SIREAD locks. As I understand it, the point of this technique is to approximate might touch through locking have touched on both rows and index ranges. I know that is considered crude by some, but the O(N^2) cost of actual predicate lock calculation would be insane in most real-world environments. I do have to concede that the paper is silent on how transactions at other isolation levels behave in this mix. On a first think-through, it doesn't seem like they would need to obtain SILOCKs for their reads, since there is no guarantee that they see things in a state which would be consistent with some serial execution of the database transactions. I don't think transactions at less stringent transaction isolation levels need to look for SILOCKs, either. I wouldn't consider my first pass thinking it through to be particularly definitive, though. That interpretation would mean, however, that while the serializable transactions would satisfy the new, more stringent requirements of recent versions of the SQL standard, they would still not provide quite the same guarantees as traditional blocking serializable transactions. In my receipting example, traditional techniques would cause the attempt to update the control record to block until the receipts on the old date committed or rolled back, and the attempt to report the day's receipts couldn't proceed until the control record update was committed, so as long as the transactions which modify data were serializable, no select at READ COMMITTED or highter could see a state inconsistent with some serial application of the serializable transactions. With this interpretation, even a SELECT-only transaction would need to be SERIALIZABLE to ensure that that it did not see the new deposit date when there were still pending receipts for the old deposit date. I think I'm OK with that if everyone else is. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, May 7, 2009 at 6:13 PM, Simon Riggs si...@2ndquadrant.com wrote: Apologies Michael, I see that my mail did remove you. That was a unconscious error; I was particularly interested in your comments regarding my assessment of the algorithmic complexity of the new theory and existing serialization technique. confusingly you didn't CC him on this message either? However on subsequent messages you attempted to re-add him but got his email address wrong. I assume everyone else got a bounce like I got? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Greg Stark st...@enterprisedb.com wrote: However on subsequent messages you attempted to re-add him but got his email address wrong. I assume everyone else got a bounce like I got? Some of my emails are getting through; some not. I haven't figured out why. I'm calling it best effort for now, and will send him a link to the thread in the archives. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Joshua D. Drake j...@commandprompt.com writes: --- a/contrib/pgbench/pgbench.c +++ b/contrib/pgbench/pgbench.c @@ -357,8 +357,6 @@ doConnect(void) return NULL; } - executeStatement(conn, SET search_path = public); - return conn; } Applied along with changes of table names accounts - pgbench_accounts etc, per discussion. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
Greg Stark st...@enterprisedb.com wrote: If I do something like SELECT count(*) FROM tab WHERE complex_function(a,b) = 5 And then you INSERT INTO tab (a,b) VALUES (1,2). How would you store any record of the fact that there's a serialization failure iff complex_function(1,2)=5 in any way that lets you look it up in any way other than evaluating complex_function for every set of values inserted? I'd be the last one to shoot down a brighter idea if someone has one, but I would assume that SELECT shown above would either resolve to a table scan, in which case you would have to have an SIREAD lock at the table level, or there would be an index on that function, in which case you could take out an SIREAD range lock on the appropriate part of the index. That said, the above would not cause a serialization failure. It would not cause any blocking. Even if both queries were concurrent, this would be fine in any order of the steps executing, and it would meet the requirements of the standard because there is *some order of serial execution* which would generate the same results as the concurrent execution -- specifically, the SELECT would appear to have run before the INSERT. It would create an edge which would be *halfway* to a problem. If the transaction doing the SELECT also modified data which was selected by some other transaction, or the transaction doing the insert also selected data which was modified by some other transaction, *then* something would need to roll back. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4beta2 release coming up
Per discussion among pgsql-core and pgsql-packagers, we're going to freeze 8.4beta2 at this time next week in preparation for releasing it during PGCon. Let's try to get some of the open items cleaned up before then ... http://wiki.postgresql.org/wiki/PostgreSQL_8.4_Open_Items regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, May 7, 2009 at 11:08 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: I would assume that SELECT shown above would either resolve to a table scan, in which case you would have to have an SIREAD lock at the table level That sounds like we're back to the MSSQL/Sybase way of doing things where you have to understand the query plan to understand why you're getting spurious serialization failures. I don't think that's terribly appealing. Consider, for example, that we might not *want* to do an index scan just because there's an index. Or there could be multiple indexes on the function, we definitely wouldn't want to have to check for range locks on every index. We have to think outside of the box and get away from the pre-existing implementations which have solutions which aren't really applicable. If we were to look at doing predicate locks in any way they would probably be stored in a whole new data structure, not related to the indexes on the table. We would need some way to index them so that we can look for conflicting locks efficiently independently from the query plan and table access methods. I've removed the broken email address for now -- please re-add the correct email address. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
On Thu, 7 May 2009, Aidan Van Dyk wrote: But by dropping the search_path, you're necessarily changing the catalog comparisons and lookups anyways, because your now taking a random search path to follow (which could have multiple entries in it) instead of one guaranteed to be a single, useable entry. You are correct here. Right now, pgbench is guaranteed to be running against a search_path with only one entry in it. If someone runs the new version against a configuration with something like: search_path='a,b,c,d,e,f,g,h,i,j,public' instead, that is going to execute more slowly than the current pgbench would have. But it seems pretty unlikely such a change would actually be noticable relative to how much per-transaction overhead and run to run variation there already is in pgbench for reasonably sized catalogs. Maybe it's worth adding a quick comment about the issue in the docs, I don't think this downside is significant enough to worry about beyond that. I think Joshua's original suggestion here is worth considering a bug fix for merging into 8.4. As long as testers don't do anything crazy with their manually set search_path, results should be identical with the earlier verions. The additional suggestion of renaming the tables with a prefix is reasonable to me, but it seems way out of scope for something to consider applying right now though. If you look at the pgbench client, there's a lot of string parsing going on in there that's not particularly efficient. I'd want to see a benchmark aimed that quantifying whether that part suffers measurably from making the table names all longer before such a change got applied. And there's already a couple of us who are in the middle of 8.4 pgbench tests that really don't need disruption like that thrown into the mix right now. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to fix search_path defencies with pg_bench
Greg Smith gsm...@gregsmith.com writes: On Thu, 7 May 2009, Aidan Van Dyk wrote: You are correct here. Right now, pgbench is guaranteed to be running against a search_path with only one entry in it. If someone runs the new version against a configuration with something like: search_path='a,b,c,d,e,f,g,h,i,j,public' instead, that is going to execute more slowly than the current pgbench would have. No, it is not. The tables will be created and used in schema 'a', and the effective search path depth will be the same. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] [Fwd: congratulations on 8.4 beta]
All, Here's a thank you note from one of our users. --Josh Berkus Original Message Subject: congratulations on 8.4 beta Date: Thu, 07 May 2009 17:59:58 -0700 From: Stan s...@icsi.berkeley.edu To: j...@postgresql.org This humble user is perpetually grateful to the PG team past and present, both for the excellent product and the calm professionalism of the community / lists -- a combination that is at times elusive in the open source world. Best regards, Stan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch needed for pg_migrator on Win32
On Win32, ShmemVariableCache needs PGDLLIMPORT for linkage of pg_migrator.so. Patch attached and applied. Tested by Hiroshi Saito. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + Index: src/include/access/transam.h === RCS file: /cvsroot/pgsql/src/include/access/transam.h,v retrieving revision 1.67 diff -c -c -r1.67 transam.h *** src/include/access/transam.h 1 Jan 2009 17:23:56 - 1.67 --- src/include/access/transam.h 8 May 2009 02:56:27 - *** *** 130,136 */ /* in transam/varsup.c */ ! extern VariableCache ShmemVariableCache; /* --- 130,136 */ /* in transam/varsup.c */ ! extern PGDLLIMPORT VariableCache ShmemVariableCache; /* -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
Tom Lane wrote: 2. There seem to be some corner cases where creating a table in the new database will not create a toast table even though there was one in the previous instance. (I'm not 100% convinced that this can happen if we create and then drop dropped columns, for instance ... but I'm not convinced it can't happen, either.) If there is a toast table in the old database then pg_migrator must bring it over because it might possibly contain live data. However, as toasting.c is presently coded there is no way to force it to create a toast table. I think we should change AlterTableCreateToastTable to add a bool force parameter. Alternatively we could add a separate entry point, but the option seems a bit cleaner. The bottom line is that the TOAST logic was so fluid on when it thinks a TOAST table is needed that even if it it consistent from 8.3 - 8.4, it would likely break in some later release and it was just safer to add a boolean. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Serializable Isolation without blocking
On Thu, 2009-05-07 at 22:47 +0100, Greg Stark wrote: On Thu, May 7, 2009 at 6:13 PM, Simon Riggs si...@2ndquadrant.com wrote: Apologies Michael, I see that my mail did remove you. That was a unconscious error; I was particularly interested in your comments regarding my assessment of the algorithmic complexity of the new theory and existing serialization technique. confusingly you didn't CC him on this message either? However on subsequent messages you attempted to re-add him but got his email address wrong. I assume everyone else got a bounce like I got? Something wrong with the email address causes it to be removed after I send. Not seen anything like that before; I'm not consciously removing Michael anyway. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some 8.4 changes needed according to pg_migrator testing
On May 7, 2009, at 12:32 PM, Tom Lane wrote: Or we could try to make the user-visible locale names platform-independent in the first place, a la David's not-silly-at-all suggestion. Actually, what I was thinking of was using a platform-independent locale infrastructure: the inconsistency in behavior between platforms is astonishing and annoying. But this works as a stopgap. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
