Re: [HACKERS] What would AggrefExprState nodes' args contain?
On Thu, Apr 28, 2011 at 4:21 PM, Vaibhav Kaushal <
vaibhavkaushal...@gmail.com> wrote:
> Thanks a lot. I was browsing the code and was thinking this would be the
> most probable scenario.
>
> But, the point is that even after removing the args initialization part in
> the ExecInitExpr for AggrefState, the sum() function is working. I believe
> that is also a aggregate function! If yes, then how is it working if I dd
> not allow the args to be initialized. The debugger says that ExecEvalAggref
> was called and the results returned are true.
>
>
Did you check the same thing with avg, or any statistical aggregates. Sum
does not need all the aggregate infrastructure in place, for example
finalisation function. May be after removing initialization part you want to
run regression (or at least aggregates.sql) to see what it breaks.
> Regards,
> Vaibhav
>
>
> On Thu, Apr 28, 2011 at 2:38 PM, Ashutosh Bapat <
> ashutosh.ba...@enterprisedb.com> wrote:
>
>> The args in AggrefExprState, are used in the functions ExecAgg,
>> ExecInitAgg and their minions to evaluate the aggregates. The
>> ExecEvalAggref() merely retrieves the results of aggregation calculated
>> during ExecAgg.
>>
>>
>> On Tue, Apr 26, 2011 at 12:04 PM, Vaibhav Kaushal <
>> vaibhavkaushal...@gmail.com> wrote:
>>
>>> Hello all,
>>>
>>> While examining the executor, I was wondering what the *args part of
>>> AggrefExprState nodes contain. I found that the Aggref (Expr)'s args list
>>> is a list of TargetEntry nodes. But the state node's args is initialized in
>>> ExecInitExpr as:
>>>
>>> astate->args = (List *) ExecInitExpr((Expr *) aggref->args,
>>> parent);
>>>
>>> This would mean that the args is actually a ExprState node list with one
>>> single item (the ExprState node / tree). I believe it potentially contains
>>> the execution tree to determine the state / value of the aggref
>>> (sub)expression. But then in the ExecEvalAggref function I do not see the
>>> args coming into picture at all! I am also unable to find a call to some
>>> function for executing the state node created in the args list. Also, no
>>> value is being extracted from that node! Why is it so?
>>>
>>> For quick reference I am adding the function (may be you don't need it
>>> but still... its a small one):
>>>
>>> /*
>>> *ExecEvalAggref
>>> *
>>> *Returns a Datum whose value is the value of the precomputed
>>> *aggregate found in the given expression context.
>>> *
>>> */
>>> static Datum
>>> ExecEvalAggref(AggrefExprState *aggref, ExprContext *econtext,
>>>bool *isNull, ExprDoneCond *isDone)
>>> {
>>>if (isDone)
>>> *isDone = ExprSingleResult;
>>>
>>> if (econtext->ecxt_aggvalues == NULL)/* safety check */
>>> elog(ERROR, "no aggregates in this expression context");
>>>
>>> *isNull = econtext->ecxt_aggnulls[aggref->aggno];
>>> return econtext->ecxt_aggvalues[aggref->aggno];
>>> }
>>>
>>>
>>> What is the use of args in AggrefExprState node here? Is it there just
>>> for some historical reason?
>>>
>>> Regards,
>>> Vaibhav
>>>
>>
>>
>>
>> --
>> Best Wishes,
>> Ashutosh Bapat
>> EntepriseDB Corporation
>> The Enterprise Postgres Company
>>
>>
>
--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company
Re: [HACKERS] Predicate locking
28.04.2011 21:36, David Fetter пишет:
On Thu, Apr 28, 2011 at 12:07:34PM +0900, Vlad Arkhipov wrote:
27.04.2011 18:38, Heikki Linnakangas пишет:
On 27.04.2011 12:24, Vlad Arkhipov wrote:
27.04.2011 17:45, Nicolas Barbier:
2011/4/27 Vlad Arkhipov:
I'm currently need predicate locking in the project, so there are two
ways
to get it by now: implement it by creating special database records
to lock
with SELECT FOR UPDATE or wait while they will be implemented in
Postgres
core. Is there something like predicate locking on the TODO list
currently?
I assume you want ("real", as opposed to what is in< 9.1 now)
SERIALIZABLE transactions, in which case you could check:
http://wiki.postgresql.org/wiki/Serializable>
Nicolas
Not sure about the whole transaction, I think it degrades the
performance too much as transactions access many tables. Just wanted
SELECT FOR UPDATE to prevent inserting records into a table with the
specified condition. It seems to be very typical situation when you have
a table like
CREATE TABLE timetable (start_ts TIMESTAMP, end_ts TIMESTAMP)
and before insertion in this table want to guarantee that there is no
overlapped time intervals there. So, first you need to lock the range in
the table, then to check if there are any records in this range.
In my case this table is the only for which I need such kind of locking.
You can do that with exclusion constraints:
http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION)
See also Depesz's blog post for a specific example on how to use it
for time ranges:
http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/
And Jeff Davis's blog post that uses the period data type instead of
the hack to represent time ranges as boxes:
http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/
Exclusion constraints works only in simple cases. I need to check a
great amount of business rules to assure that the insertion is
possible. For example,
for a table with columns (start_ts TIMESTAMP, end_ts TIMESTAMP, room
BIGINT, visitor BIGINT, service BIGINT) it's not possible to have
overlapped intervals
for the same time and room, but different visitors. So, in terms of
exclusion constraints I need something like:
room WITH =,
visitor WITH<>,
(start_ts, end_ts) WITH&&
which seems to be impossible. Predicate locking provides more
flexible way to solve this problem.
Did you actually try it? It works just fine with a timestamp range.
Cheers,
David.
Yes. It does not work on 9.0 when I add 'visitor WITH <>'.
ERROR: failed to re-find tuple within index "overlapping"
HINT: This may be because of a non-immutable index expression.
But even if it would work it would not help me anyways. Because my
constraint is much more complex and depends on other tables, I cannot
express it in terms of exclusion constraints.
--
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] Explain Nodes
On Apr 28, 2011, at 3:40 PM, Andrew Dunstan wrote: > It's been pointed out before that plugins (like FDWs) can invent their own > explain nodes, so we'll never have a canonical list of such nodes. Oh, interesting. Stil, a list of core nodes is a good 90% solution, IMHO. 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] Explain Nodes
On 04/28/2011 06:07 PM, David E. Wheeler wrote: On Apr 28, 2011, at 3:02 PM, Peter Geoghegan wrote: The code for all nodes is in src/backend/executor. I think that you will find it useful to look at the big switch statements in ExecInitNode() and friends in execProcnode.c . Yep, same as what I found in src/backend/commands/explain.c. Thanks. I'll keep using what's in src/backend/commands/explain.c because I'm using the XML format in explanation and so the nodes have human-readable names. It's been pointed out before that plugins (like FDWs) can invent their own explain nodes, so we'll never have a canonical list of such nodes. cheers andrew -- 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] Explain Nodes
On Apr 28, 2011, at 3:02 PM, Peter Geoghegan wrote: > The code for all nodes is in src/backend/executor. > > I think that you will find it useful to look at the big switch > statements in ExecInitNode() and friends in execProcnode.c . Yep, same as what I found in src/backend/commands/explain.c. Thanks. I'll keep using what's in src/backend/commands/explain.c because I'm using the XML format in explanation and so the nodes have human-readable names. 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] Explain Nodes
The code for all nodes is in src/backend/executor. I think that you will find it useful to look at the big switch statements in ExecInitNode() and friends in execProcnode.c . -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services -- 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] SQLERRD and dump of variables
Joel, On Mon, Apr 25, 2011 at 07:45:13PM +0200, Joel Jacobson wrote: > (1) Make the detailed error message available in SPs and not only the short > error message (SQLERRM) Agreed. Really, all the information available via PQresultErrorField should also be exposed in PL error handling facilities. Just exposing DETAIL as a start seems fine, but I suggest designing with that broader goal in mind. > When debugging errors in stored procedures, I often add an exception handler > and print the values of declared variables to the log. > > Unfortunately, the original detailed error message is then lost, since the > SQLERRM only contains the short message. > (2) New log field showing current values of all declared variables > > Instead of using RAISE DEBUG or customizing error messages using exception > handlers, such as, > EXCEPTION WHEN deadlock_detected > RAISE '% var_foo % var_bar %', SQLERRM, var_foo, var_bar USING ERRCODE = > 'deadlock_detected'; In the mean time, have you considered doing something like this instead? EXCEPTION WHEN deadlock_detected RAISE NOTICE '% var_foo % var_bar', var_foo, var_bar; RAISE; The information isn't as nicely aggregated, but you don't lose any details. > It would be very convenient if you could enable a log setting to write all > declared variables current values directly to the CSV log, for all errors, > to avoid the need to manually edit stored procedures to write variable > values to the log, which also means you have to wait again for the same > error to occur again, which might never happen if you have unlucky. If you go for a distinct CSV field, I think it should have a tightly-specified, machine-friendly format that all PLs populating that field must observe. If the format is going to be ad-hoc, I'd lean toward storing it as extra material in a CONTEXT field. Machine-friendly formatting wouldn't be a priority for me personally, but perhaps you or others would value it. Also keep in mind that you may have several PL/pgSQL functions in your call stack, and you'll want to capture the local variables at each level. > Instead of a new CSV log field, perhaps the setting when switch on could > append the info to the already existing "hint" field? > Example: hint: "var_foo=12345 var_bar=67890" It would belong in CONTEXT or possibly DETAIL, not HINT. HINT is for generally-applicable suggestions about the parent message, not additional facts needed to fully characterize what happened. > This would be of great help to faster track down errors. It does sound useful. I'd envision this as plpgsql_exec_error_callback checking a GUC and, when set, emitting the local variable values. Features like this do usually live in a debugger facility, not in the basic error reporting infrastructure of the language. Still, if it were in core, I'd surely use it. Consider the potential need to avoid logging very-large variable values. The GUC could perhaps be a size limit (0 disables the feature entirely), not a boolean. Thanks, nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Explain Nodes
Hackers, For my [explanation extension](http://pgxn.org/extension/explanation) I wanted to put together a list of node types, since I'm always having to figure them out to decide which nodes I'm interested in. Reading src/backend/commands/explain.c I assembled this list: + Aggregate + Append + Bitmap Heap Scan + Bitmap Index Scan + BitmapAnd + BitmapOr + CTE Scan + Foreign Scan + Function Scan + Group + Hash + Hash Join + Index Scan + Limit + LockRows + Materialize + Merge Append + Merge Join + ModifyTable + Nested Loop + Recursive Union + Result + Seq Scan + SetOp + Sort + Subquery Scan + Tid Scan + Unique + Values Scan + WindowAgg + WorkTable Scan Is that accurate? I was looking at how `sname` was set, but maybe it can be set other ways? Should a list like this be added to the EXPLAIN docs? Thanks, 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] Extreme bloating of intarray GiST indexes
On Fri, Apr 29, 2011 at 1:27 AM, Tom Lane wrote: > I seem to recall some discussion recently about documenting where you > should cut over to using "gist__intbig_ops" --- IIRC, it wasn't all that > "big" by modern standards. But it doesn't look like any such change made > it into the docs. Should we reopen that discussion? > Actually, I don't see a reason to make decision between gist__int_ops and gist__intbig_ops. Because we can choose between full enumeration and lossy bitmap on the fly on the base of array length (when some length threshold achived array is converted to bitmap). If this problem is urgent, I can write a patch with opclass that would seem more suitable to be default to me, when I'll have a time for it. With best regards, Alexander Korotkov.
Re: [HACKERS] Extreme bloating of intarray GiST indexes
Alexander Korotkov writes: > What opclass is used for GiST index: gist__int_ops or gist__intbig_ops? > Do you take into account that gist__int_ops is very inefficient for large > datasets? I seem to recall some discussion recently about documenting where you should cut over to using "gist__intbig_ops" --- IIRC, it wasn't all that "big" by modern standards. But it doesn't look like any such change made it into the docs. Should we reopen that 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] Extreme bloating of intarray GiST indexes
On Thu, Apr 28, 2011 at 11:11 PM, Josh Berkus wrote: > I'm currently looking at a database which has some extreme bloating of > intarray GiST indexes. As in 1000% bloating in only a few months. This > is not a particularly high-transaction-rate database, so the bloating is > a little surprising; I can only explain it if vacuum wasn't cleaning the > indexes at all, and maybe not even then. > > We're currently instrumenting the database so that we can collect a bit > more data on update activity, but in the meantime, has anyone seen > anything like this? What opclass is used for GiST index: gist__int_ops or gist__intbig_ops? Do you take into account that gist__int_ops is very inefficient for large datasets? With best regards, Alexander Korotkov.
Re: [HACKERS] Extension Packaging
On Apr 28, 2011, at 7:04 AM, Tom Lane wrote: > I think what we're discussing here is bug-fix revisions that don't > affect the SQL declarations for the extension. Presumably, that means a > change in the C code, so the shared library is the right place to keep > the revision number. A version number in the control file seems to > carry a nontrivial risk of being out of sync with the actual code in the > shared library. But that's exactly where it is stored right now. > What's not clear to me is whether to just suggest that extension authors > who care about this should provide a foo_version() function, or to try > to standardize it a bit more than that. Please, if those are the choices, go with the latter. If you leave it to extension authors, they'll all have different names and different return types, and will thus be worthless to most folks wanting a generalized way to see what versions of extensions they have installed. Hell, I already regret that pgtap_version() returns NUMERIC. Which reminds me, I might change it in a future version. Then it's *really* inconsistent, isn't it? > One point worth thinking about is that not all extensions will have > a shared library at all --- SQL-only extensions have been mentioned > several times as an important use case. For those, there's no such > thing as an update that doesn't change the script file, and we shouldn't > try to impose a requirement of providing a lower-level revision number. No, but there are new releases without code changes. I've been making releases that tweak documentation and the Makefile (for 9.1 support) but not the code. Should the extension in this case get a new version or not? Look, I read this thread this morning carefully, but I have to say I don't really understand it. Considering that there was consensus on not requiring any format, meaning, or mandated sort ordering of versions, there's suddenly quite a lot of discussion of the meaning and format, if not sort ordering. So maybe it's half-assed. Maybe the version can be anything but the revision must be an integer. Maybe there's a `pg_extension_version($extension_name)` function that returns ARRAY[$version, $revision], and the revision is set in the control file but not included in the version or in the upgrade file names. I think I can live with that. But, hell, you're halfway to mandating the meaning by doing this. Will we have to go the rest of the way in the future? 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] Extreme bloating of intarray GiST indexes
Josh Berkus writes: >> 1. What PG version? > 8.4.4, so it has the broken picksplit. > ... > Yeah, I'll test updating to 8.4.8. Uh, no, the picksplit bugs we fixed were in cube and seg --- there's no reason to think that updating will help this. But 8.4's pgstattuple does appear to support gist indexes, so please run that and see what you get. 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] Extreme bloating of intarray GiST indexes
> 1. What PG version? 8.4.4, so it has the broken picksplit. > 2. If new enough to have contrib/pgstattuple, what does pgstattuple() >have to say about the index? Will check. > I'm suspicious that this might be bloat caused by a bad picksplit function, > not from having a lot of dead entries in the index. We've fixed several > other bogus picksplit functions in contrib in the past. Yeah, I'll test updating to 8.4.8. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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] Extreme bloating of intarray GiST indexes
Josh Berkus writes: > I'm currently looking at a database which has some extreme bloating of > intarray GiST indexes. As in 1000% bloating in only a few months. This > is not a particularly high-transaction-rate database, so the bloating is > a little surprising; I can only explain it if vacuum wasn't cleaning the > indexes at all, and maybe not even then. > We're currently instrumenting the database so that we can collect a bit > more data on update activity, but in the meantime, has anyone seen > anything like this? 1. What PG version? 2. If new enough to have contrib/pgstattuple, what does pgstattuple() have to say about the index? I'm suspicious that this might be bloat caused by a bad picksplit function, not from having a lot of dead entries in the index. We've fixed several other bogus picksplit functions in contrib in the past. 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] ALTER TYPE DROP + composite-typed col vs. pg_upgrade
As originally noted here:
http://archives.postgresql.org/message-id/20110329215043.ga11...@tornado.gateway.2wire.net
Previous version of patch proposed here:
http://archives.postgresql.org/message-id/20110418235041.gb2...@tornado.leadboat.com
This was a side issue to that thread, and its primary issue is now resolved.
Here's a fresh thread to finish this other bug.
Now that we have ALTER TYPE DROP ATTRIBUTE, pg_dump --binary-upgrade must, for
the sake of composite-typed columns, preserve the dropped-column configuration
of stand-alone composite types. Here's a test case:
create type t as (x int, y int);
create table has_a (tcol t);
insert into has_a values ('(1,2)');
table has_a; -- (1,2)
alter type t drop attribute y cascade, add attribute z int cascade;
table has_a; -- (1,)
table has_a; -- after pg_upgrade: (1,2)
Apparently I did not fully test the last version after merging it with upstream
changes, because it did not work. Sorry for that. This version updates the
queries correctly and adds a test case. A regular "make check" passes the new
test case with or without the rest of this patch. However, a comparison of
regression database dumps before and after a pg_upgrade will reveal the problem
given this new test case. See, for example, Peter's recent patch to have the
contrib/pg_upgrade "make check" do this.
Thanks,
nm
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index afc7fd7..13ba7dd 100644
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
***
*** 7937,7942 static void
--- 7937,7943
dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
{
PQExpBuffer q = createPQExpBuffer();
+ PQExpBuffer dropped = createPQExpBuffer();
PQExpBuffer delq = createPQExpBuffer();
PQExpBuffer labelq = createPQExpBuffer();
PQExpBuffer query = createPQExpBuffer();
***
*** 7944,7952 dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
--- 7945,7957
int ntups;
int i_attname;
int i_atttypdefn;
+ int i_attlen;
+ int i_attalign;
+ int i_attisdropped;
int i_attcollation;
int i_typrelid;
int i;
+ int actual_atts;
/* Set proper schema search path so type references list correctly */
selectSourceSchema(tyinfo->dobj.namespace->dobj.name);
***
*** 7958,7990 dumpCompositeType(Archive *fout, TypeInfo *tyinfo)
* attcollation is new in 9.1. Since we only want to dump
COLLATE
* clauses for attributes whose collation is different from
their
* type's default, we use a CASE here to suppress uninteresting
!* attcollations cheaply.
*/
appendPQExpBuffer(query, "SELECT a.attname, "
"pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, "
"CASE WHEN a.attcollation <>
at.typcollation "
"THEN a.attcollation ELSE 0
END AS attcollation, "
"ct.typrelid "
! "FROM pg_catalog.pg_type ct,
pg_catalog.pg_attribute a, "
! "pg_catalog.pg_type at "
"WHERE ct.oid =
'%u'::pg_catalog.oid "
- "AND a.attrelid = ct.typrelid
"
- "AND a.atttypid = at.oid "
- "AND NOT a.attisdropped "
"ORDER BY a.attnum ",
tyinfo->dobj.catId.oid);
}
else
{
! /* We assume here that remoteVersion must be at least 70300 */
appendPQExpBuffer(query, "SELECT a.attname, "
"pg_catalog.format_type(a.atttypid, a.atttypmod) AS atttypdefn, "
"0 AS attcollation, "
"ct.typrelid "
"FROM pg_catalog.pg_type ct,
pg_catalog.pg_attribute a "
"WHERE ct.oid =
'%u'::pg_catalog.oid "
"AND a.attrelid = ct.typrelid
"
- "AND NOT a.attisdropped "
"ORDER BY a.attnum ",
tyinfo->dobj.catId.oid);
Re: [HACKERS] unknown conversion %m
> I'll make that change if Michael's happy. Sure, go ahead. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: mes...@jabber.org Go SF 49ers! Go Rhein Fire! 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
[HACKERS] Extreme bloating of intarray GiST indexes
Hackers, I'm currently looking at a database which has some extreme bloating of intarray GiST indexes. As in 1000% bloating in only a few months. This is not a particularly high-transaction-rate database, so the bloating is a little surprising; I can only explain it if vacuum wasn't cleaning the indexes at all, and maybe not even then. We're currently instrumenting the database so that we can collect a bit more data on update activity, but in the meantime, has anyone seen anything like this? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.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: [ANNOUNCE] [HACKERS] PostgreSQL Core Team
Excellent Notice Success for All Kind Best Regard Ernesto Lozano Director General Hia Technology de Venezuela ISV/ de EnterpriseDB for Venezuela , Colombia Member Community Postgresql Venezuela and Latin America www.hiatechnology.com.ve eloz...@hiatechnology.com.ve v...@postgresql.org Twitter: elozanohia Skype elozano_hia 2011/4/27 Hiroshi Saito > Congratulations!! > > > (2011/04/28 3:48), Dave Page wrote: > >> I'm pleased to announce that effective immediately, Magnus Hagander >> will be joining the PostgreSQL Core Team. >> >> Magnus has been a contributor to PostgreSQL for over 12 years, and >> played a major part in the development and ongoing maintenance of the >> native Windows port, quickly becoming a committer to help with his >> efforts. He's one of the project's webmasters and sysadmins and also >> contributes to related projects such as pgAdmin. In his spare time, he >> serves as President of the Board of PostgreSQL Europe. >> >> Regards, Dave. >> >> > > ---(end of broadcast)--- > -To unsubscribe from this list, send an email to: > > pgsql-announce-unsubscr...@postgresql.org >
Re: [HACKERS] unknown conversion %m
On 04/28/2011 12:41 PM, Tom Lane wrote: c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/interfaces/ecpg/pgtypeslib/timestamp.c:505:6: warning: unknown conversion type character 'G' in format c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/interfaces/ecpg/pgtypeslib/timestamp.c:685:6: warning: unknown conversion type character 'V' in format These are a bit interesting. According to the Single Unix Spec, %V has been standard for strftime since at least 1997, so it's damn odd if MS' version doesn't support that. OTOH, %G is *not* in that standard ... should we try to avoid using that? But it looks like all those cases are only reached if the ecpg-using application tries to use those formats, so maybe any portability risks there aren't our problem. Maybe a reasonable fix is the one that's already there for %g, ie, just prevent the gcc check from occurring. I'll make that change if Michael's happy. cheers andrew -- 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] XML with invalid chars
On 04/27/2011 05:30 PM, Noah Misch wrote:
I'm not sure what to do about the back branches and cases where data is
already in databases. This is fairly ugly. Suggestions welcome.
We could provide a script in (or linked from) the release notes for testing the
data in all your xml columns.
Here's a draft. We'd need to come up with slightly modified versions for
older versions of Postgres that don't sport array_agg() and unnest()
cheers
andrew
create function cleanup_xml_table
(schema_name text,table_name text, columns text[])
returns void
language plpgsql as
$func$
declare
cmd text;
cond text;
sep text := '';
alt text := '';
col text;
forbidden text := $$[\x1-\x8\xB\xC\xE-\x1F]$$;
begin
cmd := 'update ' || quote_ident(schema_name) || '.' ||
quote_ident(table_name) || ' set ';
for col in select unnest(columns)
loop
cmd := cmd || sep;
cond := cond || alt;
sep := ', ';
alt := ' or ';
cmd := cmd || quote_ident(col) || '=' ||
'regexp_replace(' || quote_ident(col) , || '::text, ' ||
quote_literal(forbiden) || ', , $$g$$)::xml';
cond := cond || quote_ident(col) || '::text ~ ' ||
quote_literal(forbidden);
end loop;
cmd := cmd || ' where ' || cond;
execute cmd;
return;
end;
$func$;
select cleanup_xml_table(table_schema,table_name, cols)
from
(select table_schema::text,
table_name::text,
array_agg(column_name::text) as cols
from information_schema.columns
where data_type = 'xml'
and is_updatable = 'yes'
group by table_schema, table_name) xmltabs;
--
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] SSI non-serializable UPDATE performance
On Apr 28, 2011, at 6:29 PM, "Kevin Grittner" wrote: > Robert Haas wrote: >> On Apr 28, 2011, at 9:55 AM, Dan Ports wrote: > >>> The memory barrier when acquiring the buffer page lwlock acts as >>> the synchronization point we need. When we see that no >>> serializable transactions are running, that could have been >>> reordered, but that read still had to come after the lock was >>> taken. That's all we need: even if another backend starts a >>> serializable transaction after that, we know it can't take any >>> SIREAD locks on the same target while we're holding the buffer >>> page lock. >> >> Sounds like that might be worth a comment. > > There were comments; after reading that post, do you think they need > to be expanded or reworded?: > > http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=02e6a115cc6149551527a45545fd1ef8d37e6aa0 Yeah, I think Dan's notes about memory ordering would be good to include. ...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] unknown conversion %m
Andrew Dunstan writes: > Done with that name. FYI, here is the complete set of warnings now > generated on pitta: The "unused variable" is flex's fault, not much we can do about that. Seems like most of the others could be removed with some explicit casting. > > c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/interfaces/ecpg/pgtypeslib/timestamp.c:505:6: > warning: unknown conversion type character 'G' in format > > c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/interfaces/ecpg/pgtypeslib/timestamp.c:685:6: > warning: unknown conversion type character 'V' in format These are a bit interesting. According to the Single Unix Spec, %V has been standard for strftime since at least 1997, so it's damn odd if MS' version doesn't support that. OTOH, %G is *not* in that standard ... should we try to avoid using that? But it looks like all those cases are only reached if the ecpg-using application tries to use those formats, so maybe any portability risks there aren't our problem. Maybe a reasonable fix is the one that's already there for %g, ie, just prevent the gcc check from occurring. 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] SSI non-serializable UPDATE performance
Robert Haas wrote: > On Apr 28, 2011, at 9:55 AM, Dan Ports wrote: >> The memory barrier when acquiring the buffer page lwlock acts as >> the synchronization point we need. When we see that no >> serializable transactions are running, that could have been >> reordered, but that read still had to come after the lock was >> taken. That's all we need: even if another backend starts a >> serializable transaction after that, we know it can't take any >> SIREAD locks on the same target while we're holding the buffer >> page lock. > > Sounds like that might be worth a comment. There were comments; after reading that post, do you think they need to be expanded or reworded?: http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=02e6a115cc6149551527a45545fd1ef8d37e6aa0 -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] SSI non-serializable UPDATE performance
On Apr 28, 2011, at 9:55 AM, Dan Ports wrote: > On Thu, Apr 28, 2011 at 08:43:30AM +0100, Simon Riggs wrote: >>> We added a quick return which didn't need to check any locks at the >>> front of this routine which is taken if there are no active >>> serializable transactions on the cluster at the moment of update. >> >> Surprised to hear nobody mentioning memory reordering issues about >> that, but I'm not running Itaniums anywhere. > > I did spend a while thinking about it. There aren't any memory > reordering issues with that optimization (even on the Alpha, where just > about anything goes). > > The memory barrier when acquiring the buffer page lwlock acts as the > synchronization point we need. When we see that no serializable > transactions are running, that could have been reordered, but that read > still had to come after the lock was taken. That's all we need: even if > another backend starts a serializable transaction after that, we know > it can't take any SIREAD locks on the same target while we're holding > the buffer page lock. Sounds like that might be worth a comment. ...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] unknown conversion %m
On 04/28/2011 12:30 AM, Tom Lane wrote: Andrew Dunstan writes: What I'm thinking of doing is to set up something like: #define PG_PRINTF_CHECK __printf__ and on Windows redefine it to __gnu_printf__, and then set all the formats to use PG_PRINTF_CHECK. Sound OK? +1 ... those __attribute__ declarations are messy enough already without wrapping #ifdefs around them. (Don't want to find out what pgindent would do with that ...) Possibly PG_PRINTF_ATTRIBUTE would be a better name, but he who does the work gets to pick. Done with that name. FYI, here is the complete set of warnings now generated on pitta: scan.c:16256:23: warning: unused variable 'yyg' c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/backend/port/win32/mingwcompat.c:60:1: warning: 'RegisterWaitForSingleObject' redeclared without dllimport attribute: previous dllimport ignored c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/backend/postmaster/postmaster.c:3305:2: warning: format '%d' expects type 'int', but argument 3 has type 'pgsocket' c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/backend/postmaster/postmaster.c:4810:4: warning: format '%d' expects type 'int', but argument 2 has type 'SOCKET' c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/backend/postmaster/syslogger.c:636:3: warning: format '%ld' expects type 'long int', but argument 4 has type 'intptr_t' c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/interfaces/ecpg/pgtypeslib/timestamp.c:505:6: warning: unknown conversion type character 'G' in format c:/mingw/msys/1.0/home/pgrunner/bf/root/HEAD/pgsql.3240/../pgsql/src/interfaces/ecpg/pgtypeslib/timestamp.c:685:6: warning: unknown conversion type character 'V' in format cheers andrew -- 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] PostgreSQL Core Team
On Wed, Apr 27, 2011 at 2:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. Well deserved. Congratulations! Roberto
Re: [HACKERS] TEXT vs PG_NODE_TREE in system columns (cross column and expression statistics patch)
Alvaro Herrera writes: > Excerpts from Boszormenyi Zoltan's message of jue abr 28 11:03:56 -0300 2011: >> ERROR: could not determine which collation to use for string comparison >> HINT: Use the COLLATE clause to set the collation explicitly. > Maybe the pg_node_tree problem is a bug with the collation feature. If > you could reproduce it in unpatched master, I'm sure it'd find a quick > death. Actually, I rather imagine it comes from this choice in catcache.c: /* Currently, there are no catcaches on collation-aware data types */ cache->cc_skey[i].sk_collation = InvalidOid; I'd be more worried about that if I thought it made any sense to use a pg_node_tree column as an index key, but I don't ... 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] TEXT vs PG_NODE_TREE in system columns (cross column and expression statistics patch)
Excerpts from Boszormenyi Zoltan's message of jue abr 28 11:03:56 -0300 2011: > My question is that why pg_node_tree is unusable as > syscache attribute? I attempted to alias it as text in the patch > but I get the following error if I try to use it by setting > USE_SYSCACHE_FOR_SEARCH to 1 in selfuncs.c. > Directly using the underlying pg_statistic3 doesn't cause an error. > > zozo=# select * from t1 where i+1 = 5; > ERROR: could not determine which collation to use for string comparison > HINT: Use the COLLATE clause to set the collation explicitly. Maybe the pg_node_tree problem is a bug with the collation feature. If you could reproduce it in unpatched master, I'm sure it'd find a quick death. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] TEXT vs PG_NODE_TREE in system columns (cross column and expression statistics patch)
Excerpts from Boszormenyi Zoltan's message of jue abr 28 11:03:56 -0300 2011: > Hi, > > attached is the WIP patch for cross-column statistics and > extra expression statistics. > > My question is that why pg_node_tree is unusable as > syscache attribute? I attempted to alias it as text in the patch > but I get the following error if I try to use it by setting > USE_SYSCACHE_FOR_SEARCH to 1 in selfuncs.c. > Directly using the underlying pg_statistic3 doesn't cause an error. Two comments: 1. it seems that expression stats are mostly separate from cross-column stats; does it really make sense to submit the two in the same patch? 2. there are almost no code comments anywhere 3. (bonus) if you're going to copy/paste pg_attribute.h verbatim into the new files, please remove the bits you currently have in "#if 0". (Not to mention the fact that the new catalogs seem rather poorly named). -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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] TEXT vs PG_NODE_TREE in system columns (cross column and expression statistics patch)
Boszormenyi Zoltan writes: > My question is that why pg_node_tree is unusable as > syscache attribute? I attempted to alias it as text in the patch > but I get the following error if I try to use it by setting > USE_SYSCACHE_FOR_SEARCH to 1 in selfuncs.c. > Directly using the underlying pg_statistic3 doesn't cause an error. I'm not sure what you're running into, but it doesn't matter because the design would be unworkable anyway. Expression text representations could be extremely long, too long to be usable as index keys. I don't believe either of the proposed indexes on the new catalogs are workable, actually, and the catalog definitions themselves seem a bit outre. Why are you setting it up so that stats on expressions and cross-column stats are mutually exclusive? The idea that's used currently is that we only compute stats on expressions that are indexed, so the OID/attnum of the index column can be used as a reference in pg_statistic. I don't see a strong need to deviate from that approach. 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] Extension Packaging
On Thu, Apr 28, 2011 at 3:04 PM, Tom Lane wrote: > Daniele Varrazzo writes: >> On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen wrote: >>> How about each .so containing a version callback? >>> >>> Thus you can show what is the version of underlying implementation >>> without needing to mess with catalogs just to keep track of patchlevel >>> of C code. > >> On this line, it would be easier to add a parameter "revision" to the >> control file and have a function pg_revision(ext) to return it, >> eventually showing in the \dx output. > > I think what we're discussing here is bug-fix revisions that don't > affect the SQL declarations for the extension. Presumably, that means a > change in the C code, so the shared library is the right place to keep > the revision number. A version number in the control file seems to > carry a nontrivial risk of being out of sync with the actual code in the > shared library. There is also the case of extensions whose data file matter: for instance I've packaged the Italian text search dictionary as an extension (http://pgxn.org/dist/italian_fts/): it contains no .so but it may happen for the dictionary files to be changed. Its version is 1.2 and will stay so as long as the sql doesn't change, but its revision is currently 1.2.1 and may bump to 1.2.2 should the dict content change. For this extension, just spotting the 1.2.1 in the \dx output would be more than enough, I don't see any use for the revision number returned in an api call. As long as the extension is installed via "make install" the .control shouldn't drift away from the extension files it represents. -- Daniele -- 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] unknown conversion %m
Andrew Dunstan writes: > Yeah, I think that the underscore variants got added because of cases > like ours where printf is sometimes defined as a macro. I'll just need > to make sure that this gets set before there's any possibility of that > happening. The existing code would already be broken if that were the case, so I see no need to worry. 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] Extension Packaging
Daniele Varrazzo writes: > On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen wrote: >> How about each .so containing a version callback? >> >> Thus you can show what is the version of underlying implementation >> without needing to mess with catalogs just to keep track of patchlevel >> of C code. > On this line, it would be easier to add a parameter "revision" to the > control file and have a function pg_revision(ext) to return it, > eventually showing in the \dx output. I think what we're discussing here is bug-fix revisions that don't affect the SQL declarations for the extension. Presumably, that means a change in the C code, so the shared library is the right place to keep the revision number. A version number in the control file seems to carry a nontrivial risk of being out of sync with the actual code in the shared library. What's not clear to me is whether to just suggest that extension authors who care about this should provide a foo_version() function, or to try to standardize it a bit more than that. One point worth thinking about is that not all extensions will have a shared library at all --- SQL-only extensions have been mentioned several times as an important use case. For those, there's no such thing as an update that doesn't change the script file, and we shouldn't try to impose a requirement of providing a lower-level revision number. 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] unknown conversion %m
On 04/28/2011 12:44 AM, Tom Lane wrote: Andrew Dunstan writes: What I'm thinking of doing is to set up something like: #define PG_PRINTF_CHECK __printf__ BTW, gcc 2.95.3 documents "printf", and not "__printf__". Suggest not including the underscores, since that's apparently a johnny-come-lately spelling. It's not like any of this construct is even faintly portable to non-gcc compilers anyway ... Yeah, I think that the underscore variants got added because of cases like ours where printf is sometimes defined as a macro. I'll just need to make sure that this gets set before there's any possibility of that happening. cheers andrew -- 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] SIREAD lock versus ACCESS EXCLUSIVE lock
Simon Riggs wrote: > On Wed, Apr 27, 2011 at 8:59 PM, Kevin Grittner > wrote: > >> For correct serializable behavior in the face of concurrent DDL >> execution, I think that a request for a heavyweight ACCESS >> EXCLUSIVE lock might need to block until all SIREAD locks on the >> relation have been released. Picture, for example, what might >> happen if one transaction acquires some predicate locks, then >> commits (releasing its heavyweight lock on the table), and before >> concurrent READ WRITE transactions complete there is a CLUSTER on >> the table. Or a DROP INDEX. :-( > > Sorry, I can't picture it. What will happen? Rather than get into a complex generalized discussion, I'll provide the simplest example I can picture. Let's say we have two concurrent transactions, T0 and T1. Up to this point T0 has read from table x and written to table y based on what was read from x. T1 has read from y -- but since the transactions are concurrent, it doesn't see T0's write. Let's assume each read was of a single tuple accessed through a btree index, so each transaction has one tuple lock on the heap and one page lock on the index. Now T0 commits. T0 must hold its SIREAD locks because of concurrent transaction T1. Everything is fine so far. Now a DBA runs CLUSTER against table x. The SIREAD locks held by T0 are probably now wrong, because the tuple and its index entry are likely to have moved. Now T1 writes to table x based on what it read from y. It could incorrectly detect a conflict if it happens to write to a tuple at the locked block and tuple number when it's not the same row. Worse, it could miss detecting a conflict if it's really updating the same row that T0 wrote, and that's not detected because it's not at the locked location any more. >> If this change is too destabilizing for this point in the release >> we could document it as a limitation and fix it in 9.2. > > I don't think this should wait for 9.2 > > It either works, or it doesn't. Putting caveats in there will just > detract from people's belief in it. I see your point. And this clearly is a bug. We failed to consider this category of problem and cover it. Heikki's suggestion is clearly the best plan. In the example above, when the CLUSTER was run it would make a call to the predicate locking module telling it to promote all SIREAD locks for table x or any of its indexes into a relation level lock on table x. The CLUSTER would cause us to lose the finer granularity of the locks on the table, and in this example if T1 wrote to table x it be rolled back with a serialization failure. This could be a false positive, but we expect to have some of those -- the transaction is retried and then succeeds. You can't have a false negative, so integrity is preserved. I'll try to work up a detailed plan of which commands need what actions. For example, DROP INDEX needs to promote SIREAD locks on the dropped index to relation locks on the related table. TRUNCATE TABLE is a little confusing -- I think that if it's run in a serializable transaction we generate a rw-conflict out from that transaction to every transaction holding any SIREAD lock on that table or any of its indexes, and then clear those SIREAD locks. This'll take some study. -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] improvements to pgtune
That's some great starting advice there. I have a couple of final exams in the next 36 hours. Will get to work almost immediately after that. I will definitely take small steps before going for some of the tougher tasks. I would of-course like this conversation to go on, so I can see a more comprehensive TODO list. One of my first tasks on GSoC is to make sure I create a good project specification document. So there can be definite expectations and targets. This conversation helps me do that! Regards, Shiv On Thu, Apr 28, 2011 at 9:50 AM, Greg Smith wrote: > Shiv wrote: > >> On the program I hope to learn as much about professional software >> engineering principles as PostgreSQL. My project is aimed towards extending >> and hopefully improving upon pgtune. If any of you have some ideas or >> thoughts to share. I am all ears!! >> > > Well, first step on the software engineering side is to get a copy of the > code in a form you can modify. I'd recommend grabbing it from > https://github.com/gregs1104/pgtune ; while there is a copy of the program > on git.postgresql.org, it's easier to work with the one on github instead. > I can push updates over to the copy on postgresql.org easily enough, and > that way you don't have to worry about getting an account on that server. > > There's a long list of suggested improvements to make at > https://github.com/gregs1104/pgtune/blob/master/TODO > > Where I would recommend getting started is doing some of the small items on > there, some of which I have already put comments into the code about but > just not finished yet. Some examples: > > -Validate against min/max > -Show original value in output > -Limit shared memory use on Windows (see notes on shared_buffers at > http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server for more > information) > -Look for postgresql.conf file using PGDATA environment variable > -Look for settings files based on path of the pgtune executable > -Save a settings reference files for newer versions of PostgreSQL (right > now I only target 8.4) and allow passing in the version you're configuring. > > A common mistake made by GSOC students is to dive right in to trying to > make big changes. You'll be more successful if you get practice at things > like preparing and sharing patches on smaller changes first. > > At the next level, there are a few larger features that I would consider > valuable that are not really addressed by the program yet: > > -Estimate how much shared memory is used by the combination of settings. > See Table 17-2 at > http://www.postgresql.org/docs/9.0/static/kernel-resources.html ; those > numbers aren't perfect, and improving that table is its own useful project. > But it gives an idea how they fit together. I have some notes at the end > of the TODO file on how I think the information needed to produce this needs > to be passed around the inside of pgtune. > > -Use that estimate to produce a sysctl.conf file for one platform; Linux is > the easiest one to start with. I've attached a prototype showing how to do > that, written in bash. > > -Write a Python-TK or web-based front-end for the program. > > Now that I know someone is going to work on this program again, I'll see > what I can do to clean some parts of it up. There are a couple of things > it's easier for me to just fix rather than to describe, like the way I > really want to change how it adds comments to the settings it changes. > > -- > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us > > > > #!/bin/bash > > # Output lines suitable for sysctl configuration based > # on total amount of RAM on the system. The output > # will allow up to 50% of physical memory to be allocated > # into shared memory. > > # On Linux, you can use it as follows (as root): > # > # ./shmsetup >> /etc/sysctl.conf > # sysctl -p > > # Early FreeBSD versions do not support the sysconf interface > # used here. The exact version where this works hasn't > # been confirmed yet. > > page_size=`getconf PAGE_SIZE` > phys_pages=`getconf _PHYS_PAGES` > > if [ -z "$page_size" ]; then > echo Error: cannot determine page size > exit 1 > fi > > if [ -z "$phys_pages" ]; then > echo Error: cannot determine number of memory pages > exit 2 > fi > > shmall=`expr $phys_pages / 2` > shmmax=`expr $shmall \* $page_size` > > echo \# Maximum shared segment size in bytes > echo kernel.shmmax = $shmmax > echo \# Maximum number of shared memory segments in pages > echo kernel.shmall = $shmall > >
Re: [HACKERS] Extension Packaging
On Thu, Apr 28, 2011 at 4:40 PM, Daniele Varrazzo wrote: > On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen wrote: >> On Thu, Apr 28, 2011 at 4:07 PM, Daniele Varrazzo >> wrote: >>> On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine >>> wrote: Tom Lane writes: > If you didn't change the install script then it's not necessary to > execute ALTER EXTENSION ... UPGRADE. You seem to be assuming that the > pg_extensions catalog has to reflect the bug fix level of an extension, > but that is *not* the intention. If it did reflect that, you'd need > N times as many upgrade scripts, most of them identical, to deal with > updating from different bug fix levels of the prior version. +1 — but this discussion shows we're not exactly finished here. >>> >>> Probably what is needed is only a clarification that the version >>> number is only about schema object, not revision, patch level, release >>> status or whatever else semantically meaningful. I've attached a patch >>> for the docs about the point. >> >> How about each .so containing a version callback? >> >> Thus you can show what is the version of underlying implementation >> without needing to mess with catalogs just to keep track of patchlevel >> of C code. > > On this line, it would be easier to add a parameter "revision" to the > control file and have a function pg_revision(ext) to return it, > eventually showing in the \dx output. But this still assumes the > revision as being just a string, and if it has a semantic meaning then > it requires parsing to extract meaning for it (whereas foo_revision() > may return everything the author of foo thinks is important for code > depending on it to know, e.g. it may return an integer 90102 or a > record (major, minor, patch, status, svn-rev, > name-of-my-last-daughter). I don't think we want to force any > convention, such as the revision being a semver number - even if PGXN > restrict the extension to this strings subset. Yeah, I was thinking about such convertionless patchlevel, just for information. Authors would use it for patchlevel, but packages could put their version numbers there too. Main idea would be to see the noise versions also in db, otherwise you still need to go to OS to see whats actually installed. Reading it from control file seems even better solution for that, although there is minor problem of running backend using older .so-s than installed. But that does not seem serious enough to warrant a workaround. -- marko -- 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] Extension Packaging
On Thu, Apr 28, 2011 at 2:21 PM, Marko Kreen wrote: > On Thu, Apr 28, 2011 at 4:07 PM, Daniele Varrazzo > wrote: >> On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine >> wrote: >>> Tom Lane writes: If you didn't change the install script then it's not necessary to execute ALTER EXTENSION ... UPGRADE. You seem to be assuming that the pg_extensions catalog has to reflect the bug fix level of an extension, but that is *not* the intention. If it did reflect that, you'd need N times as many upgrade scripts, most of them identical, to deal with updating from different bug fix levels of the prior version. >>> >>> +1 — but this discussion shows we're not exactly finished here. >> >> Probably what is needed is only a clarification that the version >> number is only about schema object, not revision, patch level, release >> status or whatever else semantically meaningful. I've attached a patch >> for the docs about the point. > > How about each .so containing a version callback? > > Thus you can show what is the version of underlying implementation > without needing to mess with catalogs just to keep track of patchlevel > of C code. On this line, it would be easier to add a parameter "revision" to the control file and have a function pg_revision(ext) to return it, eventually showing in the \dx output. But this still assumes the revision as being just a string, and if it has a semantic meaning then it requires parsing to extract meaning for it (whereas foo_revision() may return everything the author of foo thinks is important for code depending on it to know, e.g. it may return an integer 90102 or a record (major, minor, patch, status, svn-rev, name-of-my-last-daughter). I don't think we want to force any convention, such as the revision being a semver number - even if PGXN restrict the extension to this strings subset. -- Daniele -- 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] PostgreSQL Core Team
> > On Apr 27, 2011 1:49 PM, "Dave Page" wrote: >> >> I'm pleased to announce that effective immediately, Magnus Hagander >> will be joining the PostgreSQL Core Team. >> >> Magnus has been a contributor to PostgreSQL for over 12 years, and >> played a major part in the development and ongoing maintenance of the >> native Windows port, quickly becoming a committer to help with his >> efforts. He's one of the project's webmasters and sysadmins and also >> contributes to related projects such as pgAdmin. In his spare time, he >> serves as President of the Board of PostgreSQL Europe. > Many congratulations Magnus! You fully deserve it. Thanks, Pavan -- Pavan Deolasee 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] VX_CONCURRENT flag on vxfs( 5.1 or later) for performance for postgresql?
HSIEN-WEN CHU writes: > When database files are on a VxFS filesystem, performance can be > significantly improved by setting the VX_CONCURRENT cache advisory on > the file according to vxfs document, Presumably, if whatever behavior this invokes were an unalloyed good, they'd have just made it the default. The existence of a flag makes me suppose that there are some clear application-visible downsides. What are they? BTW, please do not cross-post the same question to three different lists. 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] Extension Packaging
On Thu, Apr 28, 2011 at 4:07 PM, Daniele Varrazzo wrote: > On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine > wrote: >> Tom Lane writes: >>> If you didn't change the install script then it's not necessary to >>> execute ALTER EXTENSION ... UPGRADE. You seem to be assuming that the >>> pg_extensions catalog has to reflect the bug fix level of an extension, >>> but that is *not* the intention. If it did reflect that, you'd need >>> N times as many upgrade scripts, most of them identical, to deal with >>> updating from different bug fix levels of the prior version. >> >> +1 — but this discussion shows we're not exactly finished here. > > Probably what is needed is only a clarification that the version > number is only about schema object, not revision, patch level, release > status or whatever else semantically meaningful. I've attached a patch > for the docs about the point. How about each .so containing a version callback? Thus you can show what is the version of underlying implementation without needing to mess with catalogs just to keep track of patchlevel of C code. -- marko -- 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] Extension Packaging
On Wed, Apr 27, 2011 at 1:48 PM, Dimitri Fontaine
wrote:
> Tom Lane writes:
>> If you didn't change the install script then it's not necessary to
>> execute ALTER EXTENSION ... UPGRADE. You seem to be assuming that the
>> pg_extensions catalog has to reflect the bug fix level of an extension,
>> but that is *not* the intention. If it did reflect that, you'd need
>> N times as many upgrade scripts, most of them identical, to deal with
>> updating from different bug fix levels of the prior version.
>
> +1 — but this discussion shows we're not exactly finished here.
Probably what is needed is only a clarification that the version
number is only about schema object, not revision, patch level, release
status or whatever else semantically meaningful. I've attached a patch
for the docs about the point.
>> IMO it'd be better if the bug fix level was tracked outside the
>> database, for instance via an RPM package version/release number.
>> I'm not sure whether PGXN has anything for that at the moment.
>
> -0.5
>
> What I think would be useful here is to have both version and revision
> in the control file and pg_extension catalog. Then an extension can
> easily be at version 1.2 and revision 1.2.3.
>
> Now, that means that ALTER EXTENSION UPGRADE should accept to upgrade
> the revision in the control file when nothing else changes.
A less invasive change would be to just update the extension comment
on ALTER EXTENSION UPGRADE. This means that the revision would be just
informative and not metadata available to eventual depending code but
it's on purpose. I think that, if an extension requires its patchlevel
to be known, e.g. because depending code has to take different actions
based on the revision, it should really provide an inspection
function, such as foo_revision(), so that pre-9.1 code can work with
it as well.
-- Daniele
From 03fa593a46f1dae0a8e83b4bccd6dea51e2c102c Mon Sep 17 00:00:00 2001
From: Daniele Varrazzo
Date: Thu, 28 Apr 2011 14:02:08 +0100
Subject: [PATCH] Added paragraph about the distinction between extension version and patch level.
---
doc/src/sgml/extend.sgml | 14 ++
1 files changed, 14 insertions(+), 0 deletions(-)
diff --git a/doc/src/sgml/extend.sgml b/doc/src/sgml/extend.sgml
index 4ca17ef..ad26f5a 100644
--- a/doc/src/sgml/extend.sgml
+++ b/doc/src/sgml/extend.sgml
@@ -767,6 +767,20 @@ SELECT pg_catalog.pg_extension_config_dump('my_config', 'WHERE NOT standard_entr
+ Note that version names are only meant to give an identity to the set of
+ objects in the database schema and should not be used to track outside
+ objects such as shared libraries or data files. Specifically, if the
+ extension has a concept of revision or patch
+ level (maybe loaded with semantic meaning such as revisions order or
+ release status), setting a version equal to the patch level is
+ discouraged as it would require a large number of mostly equal (or empty)
+ upgrade scripts. For example, if a bug is found in the C code of the
+ extension foo version 1.0 you may want to release
+ a revision 1.0.1 but you should leave the version as
+ 1.0 if no object in the database schema is changed.
+
+
+
Sometimes it is useful to provide downgrade scripts, for
example foo--1.1--1.0.sql to allow reverting the changes
associated with version 1.1. If you do that, be careful
--
1.7.1
--
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] PostgreSQL Core Team
On Apr 27, 2011 1:49 PM, "Dave Page" wrote: > > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenance of the > native Windows port, quickly becoming a committer to help with his > efforts. He's one of the project's webmasters and sysadmins and also > contributes to related projects such as pgAdmin. In his spare time, he > serves as President of the Board of PostgreSQL Europe. Grattis, Magnus. :-) -selena
Re: [HACKERS] Predicate locking
On Thu, Apr 28, 2011 at 12:07:34PM +0900, Vlad Arkhipov wrote:
> 27.04.2011 18:38, Heikki Linnakangas пишет:
> >On 27.04.2011 12:24, Vlad Arkhipov wrote:
> >>27.04.2011 17:45, Nicolas Barbier:
> >>>2011/4/27 Vlad Arkhipov:
> >>>
> I'm currently need predicate locking in the project, so there are two
> ways
> to get it by now: implement it by creating special database records
> to lock
> with SELECT FOR UPDATE or wait while they will be implemented in
> Postgres
> core. Is there something like predicate locking on the TODO list
> currently?
> >>>I assume you want ("real", as opposed to what is in< 9.1 now)
> >>>SERIALIZABLE transactions, in which case you could check:
> >>>
> >>>http://wiki.postgresql.org/wiki/Serializable>
> >>>
> >>>Nicolas
> >>>
> >>Not sure about the whole transaction, I think it degrades the
> >>performance too much as transactions access many tables. Just wanted
> >>SELECT FOR UPDATE to prevent inserting records into a table with the
> >>specified condition. It seems to be very typical situation when you have
> >>a table like
> >>CREATE TABLE timetable (start_ts TIMESTAMP, end_ts TIMESTAMP)
> >>and before insertion in this table want to guarantee that there is no
> >>overlapped time intervals there. So, first you need to lock the range in
> >>the table, then to check if there are any records in this range.
> >>In my case this table is the only for which I need such kind of locking.
> >
> >You can do that with exclusion constraints:
> >
> >http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION)
> >
> >
> >See also Depesz's blog post for a specific example on how to use it
> >for time ranges:
> >
> >http://www.depesz.com/index.php/2010/01/03/waiting-for-8-5-exclusion-constraints/
> >
> >
> >And Jeff Davis's blog post that uses the period data type instead of
> >the hack to represent time ranges as boxes:
> >
> >http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/
> >
> Exclusion constraints works only in simple cases. I need to check a
> great amount of business rules to assure that the insertion is
> possible. For example,
> for a table with columns (start_ts TIMESTAMP, end_ts TIMESTAMP, room
> BIGINT, visitor BIGINT, service BIGINT) it's not possible to have
> overlapped intervals
> for the same time and room, but different visitors. So, in terms of
> exclusion constraints I need something like:
>
> room WITH =,
> visitor WITH <>,
> (start_ts, end_ts) WITH &&
>
> which seems to be impossible. Predicate locking provides more
> flexible way to solve this problem.
Did you actually try it? It works just fine with a timestamp range.
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
--
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] Best way to construct Datum out of a string?
Am 28.04.2011 05:52, schrieb Tom Lane: > =?ISO-8859-15?Q?Yves_Wei=DFig?= > writes: >> Am 27.04.2011 16:11, schrieb Heikki Linnakangas: >>> What kind of a Datum do you want it to be? What data type? See >>> CStringGetDatum, or perhaps CStringGetTextDatum(). Or perhaps you want >>> to call the input function of some other datatype, with InputFunctionCall. > >> Ok, but how do I do that? > >> Currently I am using: > >> _ebi_mtab_insert(rel, CStringGetTextDatum(BVEC_NULL), bin_enc); > >> This function does not mere than hashing the 2nd passed argument (with >> the internal hash functions of hash.c) but each time a different hash >> value is returned, so I am thinking I might pass a pointer and not the >> real Datum. I am highly irritated now... as for now I thought I >> understood Datum... > > Well, it's hard to say for sure when you haven't shown us either what > BVEC_NULL means or what _ebi_mtab_insert is doing with the value it gets > ... but in fact a text Datum *is* a pointer, as is the Datum value for > any other pass-by-reference type. Datum isn't magic, it's only a > pointer-sized integer type. For anything bigger than that, the Datum > value is a pointer to some data somewhere else. > > regards, tom lane > Sorry for giving so little information. I found the bug myself, I was trying to hash a Datum created with CStringGetTextDatum with hashint4, I certainly noticed this when I looked at the function which was actually called by: hash_value = DatumGetUInt32(FunctionCall1(procinfo, value)); Thanks for trying to help! Yves -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] new AM, best way to obtain new block at end of index?
Hi list, currently I am obtaining a new block at the end of an index with: buf = ReadBuffer(rel, P_NEW); but it throws: ERROR: unexpected data beyond EOF in block 0 of relation base/11874/156053 HINT: This has been seen to occur with buggy kernels; consider updating your system. system is up to date: $ uname -r 2.6.35-28-generic Is there another way to do it? What could be the source of the problem? Yves -- 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] What would AggrefExprState nodes' args contain?
Thanks a lot. I was browsing the code and was thinking this would be the
most probable scenario.
But, the point is that even after removing the args initialization part in
the ExecInitExpr for AggrefState, the sum() function is working. I believe
that is also a aggregate function! If yes, then how is it working if I dd
not allow the args to be initialized. The debugger says that ExecEvalAggref
was called and the results returned are true.
Regards,
Vaibhav
On Thu, Apr 28, 2011 at 2:38 PM, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:
> The args in AggrefExprState, are used in the functions ExecAgg, ExecInitAgg
> and their minions to evaluate the aggregates. The ExecEvalAggref() merely
> retrieves the results of aggregation calculated during ExecAgg.
>
>
> On Tue, Apr 26, 2011 at 12:04 PM, Vaibhav Kaushal <
> vaibhavkaushal...@gmail.com> wrote:
>
>> Hello all,
>>
>> While examining the executor, I was wondering what the *args part of
>> AggrefExprState nodes contain. I found that the Aggref (Expr)'s args list
>> is a list of TargetEntry nodes. But the state node's args is initialized in
>> ExecInitExpr as:
>>
>> astate->args = (List *) ExecInitExpr((Expr *) aggref->args,
>> parent);
>>
>> This would mean that the args is actually a ExprState node list with one
>> single item (the ExprState node / tree). I believe it potentially contains
>> the execution tree to determine the state / value of the aggref
>> (sub)expression. But then in the ExecEvalAggref function I do not see the
>> args coming into picture at all! I am also unable to find a call to some
>> function for executing the state node created in the args list. Also, no
>> value is being extracted from that node! Why is it so?
>>
>> For quick reference I am adding the function (may be you don't need it but
>> still... its a small one):
>>
>> /*
>> *ExecEvalAggref
>> *
>> *Returns a Datum whose value is the value of the precomputed
>> *aggregate found in the given expression context.
>> *
>> */
>> static Datum
>> ExecEvalAggref(AggrefExprState *aggref, ExprContext *econtext,
>>bool *isNull, ExprDoneCond *isDone)
>> {
>>if (isDone)
>> *isDone = ExprSingleResult;
>>
>> if (econtext->ecxt_aggvalues == NULL)/* safety check */
>> elog(ERROR, "no aggregates in this expression context");
>>
>> *isNull = econtext->ecxt_aggnulls[aggref->aggno];
>> return econtext->ecxt_aggvalues[aggref->aggno];
>> }
>>
>>
>> What is the use of args in AggrefExprState node here? Is it there just for
>> some historical reason?
>>
>> Regards,
>> Vaibhav
>>
>
>
>
> --
> Best Wishes,
> Ashutosh Bapat
> EntepriseDB Corporation
> The Enterprise Postgres Company
>
>
Re: [HACKERS] Extension Packaging
Tom Lane writes: > If you didn't change the install script then it's not necessary to > execute ALTER EXTENSION ... UPGRADE. You seem to be assuming that the > pg_extensions catalog has to reflect the bug fix level of an extension, > but that is *not* the intention. If it did reflect that, you'd need > N times as many upgrade scripts, most of them identical, to deal with > updating from different bug fix levels of the prior version. +1 — but this discussion shows we're not exactly finished here. > IMO it'd be better if the bug fix level was tracked outside the > database, for instance via an RPM package version/release number. > I'm not sure whether PGXN has anything for that at the moment. -0.5 What I think would be useful here is to have both version and revision in the control file and pg_extension catalog. Then an extension can easily be at version 1.2 and revision 1.2.3. Now, that means that ALTER EXTENSION UPGRADE should accept to upgrade the revision in the control file when nothing else changes. > We've been over that in the previous discussions, please see the > archives. I believe the conclusion was that breaking ABI compatibility > within an update is just not a good idea. IOW, ABI should be tied to version, not to revision, I think. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et 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] What would AggrefExprState nodes' args contain?
The args in AggrefExprState, are used in the functions ExecAgg, ExecInitAgg
and their minions to evaluate the aggregates. The ExecEvalAggref() merely
retrieves the results of aggregation calculated during ExecAgg.
On Tue, Apr 26, 2011 at 12:04 PM, Vaibhav Kaushal <
vaibhavkaushal...@gmail.com> wrote:
> Hello all,
>
> While examining the executor, I was wondering what the *args part of
> AggrefExprState nodes contain. I found that the Aggref (Expr)'s args list
> is a list of TargetEntry nodes. But the state node's args is initialized in
> ExecInitExpr as:
>
> astate->args = (List *) ExecInitExpr((Expr *) aggref->args,
> parent);
>
> This would mean that the args is actually a ExprState node list with one
> single item (the ExprState node / tree). I believe it potentially contains
> the execution tree to determine the state / value of the aggref
> (sub)expression. But then in the ExecEvalAggref function I do not see the
> args coming into picture at all! I am also unable to find a call to some
> function for executing the state node created in the args list. Also, no
> value is being extracted from that node! Why is it so?
>
> For quick reference I am adding the function (may be you don't need it but
> still... its a small one):
>
> /*
> *ExecEvalAggref
> *
> *Returns a Datum whose value is the value of the precomputed
> *aggregate found in the given expression context.
> *
> */
> static Datum
> ExecEvalAggref(AggrefExprState *aggref, ExprContext *econtext,
>bool *isNull, ExprDoneCond *isDone)
> {
>if (isDone)
> *isDone = ExprSingleResult;
>
> if (econtext->ecxt_aggvalues == NULL)/* safety check */
> elog(ERROR, "no aggregates in this expression context");
>
> *isNull = econtext->ecxt_aggnulls[aggref->aggno];
> return econtext->ecxt_aggvalues[aggref->aggno];
> }
>
>
> What is the use of args in AggrefExprState node here? Is it there just for
> some historical reason?
>
> Regards,
> Vaibhav
>
--
Best Wishes,
Ashutosh Bapat
EntepriseDB Corporation
The Enterprise Postgres Company
Re: [HACKERS] [ANNOUNCE] PostgreSQL Core Team
Auguri! On Wed, Apr 27, 2011 at 8:48 PM, Dave Page wrote: > I'm pleased to announce that effective immediately, Magnus Hagander > will be joining the PostgreSQL Core Team. > > Magnus has been a contributor to PostgreSQL for over 12 years, and > played a major part in the development and ongoing maintenance of the > native Windows port, quickly becoming a committer to help with his > efforts. He's one of the project's webmasters and sysadmins and also > contributes to related projects such as pgAdmin. In his spare time, he > serves as President of the Board of PostgreSQL Europe. > > Regards, Dave. > > -- > Dave Page > PostgreSQL Core Team > http://www.postgresql.org/ > > ---(end of broadcast)--- > -To unsubscribe from this list, send an email to: > > pgsql-announce-unsubscr...@postgresql.org > -- Cordiali saluti, Valeriano Cossu Regards, Valeriano Cossu ph: (0039) 346 2187419 http://www.valerianocossu.com skype: valerianocossu -- 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] SSI non-serializable UPDATE performance
On Thu, Apr 28, 2011 at 08:43:30AM +0100, Simon Riggs wrote: > > We added a quick return which didn't need to check any locks at the > > front of this routine which is taken if there are no active > > serializable transactions on the cluster at the moment of update. > > Surprised to hear nobody mentioning memory reordering issues about > that, but I'm not running Itaniums anywhere. I did spend a while thinking about it. There aren't any memory reordering issues with that optimization (even on the Alpha, where just about anything goes). The memory barrier when acquiring the buffer page lwlock acts as the synchronization point we need. When we see that no serializable transactions are running, that could have been reordered, but that read still had to come after the lock was taken. That's all we need: even if another backend starts a serializable transaction after that, we know it can't take any SIREAD locks on the same target while we're holding the buffer page lock. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.net/ -- 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] SIREAD lock versus ACCESS EXCLUSIVE lock
On Wed, Apr 27, 2011 at 8:59 PM, Kevin Grittner wrote: > For correct serializable behavior in the face of concurrent DDL > execution, I think that a request for a heavyweight ACCESS EXCLUSIVE > lock might need to block until all SIREAD locks on the relation have > been released. Picture, for example, what might happen if one > transaction acquires some predicate locks, then commits (releasing > its heavyweight lock on the table), and before concurrent READ WRITE > transactions complete there is a CLUSTER on the table. Or a DROP > INDEX. :-( Sorry, I can't picture it. What will happen? > Both require an ACCESS EXCLUSIVE lock. Since an active transaction > would already have an ACCESS SHARE lock when acquiring the SIREAD > locks, this couldn't block in the other direction or with an active > transaction. That means that it couldn't cause any deadlocks if we > added blocking to the acquisition of an ACCESS EXCLUSIVE based on > this. > > If we don't do this I don't think that there is a more serious > impact than inaccurate conflict detection for serializable > transactions which are active when these operations are performed. > Well, that and the possibility of seeing SIRead locks in the > pg_locks view for indexes or tables which no longer exist. So far I > don't see any crash modes or effects on non-serializable > transactions. If this change is too destabilizing for this point in > the release we could document it as a limitation and fix it in 9.2. I don't think this should wait for 9.2 It either works, or it doesn't. Putting caveats in there will just detract from people's belief in it. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] SSI non-serializable UPDATE performance
On Wed, Apr 27, 2011 at 7:15 PM, Kevin Grittner wrote: > (1) If a tuple which is predicate locked, or sits on a predicate- > locked page, is updated, the predicate lock is duplicated for the > new tuple. We have found patterns of updates involving four or more > transactions where a non-serializable transaction can hide > serialization anomalies among serializable transactions if we don't > do this. Someone suggested that we could take out this call and > just document that serializable transactions may not comply with the > standard-defined behavior when there are concurrent non-serializable > transactions. We were unable to show a measurable performance hit > on this, although this was just with 32 clients hitting a 16 > processor machine. There was at least a theoretical possibility > that with higher levels of concurrency there could have been a new > contention point for a LW lock here which could affect performance. > We added a quick return which didn't need to check any locks at the > front of this routine which is taken if there are no active > serializable transactions on the cluster at the moment of update. Surprised to hear nobody mentioning memory reordering issues about that, but I'm not running Itaniums anywhere. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] SSI non-serializable UPDATE performance
On Wed, Apr 27, 2011 at 7:15 PM, Kevin Grittner wrote: > Simon Riggs wrote: > >> Reading the code, IIUC, we check for RW conflicts after each write >> but only if the writer is running a serializable transaction. > > Correct as far as that statement goes. Thanks. I'm surprised by that though, it seems weird. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
