Re: [HACKERS] row security roadmap proposal
On 12/18/2013 01:03 AM, Robert Haas wrote: > On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith > wrote: >> > On 12/16/13 9:36 AM, Craig Ringer wrote: >>> >> >>> >> - Finish and commit updatable security barrier views. I've still got a >>> >> lot of straightening out to do there. >> > >> > I don't follow why you've put this part first. It has a lot of new >> > development and the risks that go along with that, but the POC projects >> > I've >> > been testing are more interested in the view side issues. > I don't really see a way that any of this can work without that. To > be clear, that work is required even just for read-side security. It's possible to build limited read-side-only security on top of the existing s.b. views as they stand, with no update support. You can grant write-only access to the base relations, and require people to use a different relation name / schema when they want to access a relation for write vs for read. You can't use RETURNING, and you can still learn from result rowcounts etc. It's clumsy but usable-ish. So it works - as long as you're using absolutely 100% read-only access for users you need to constrain, or you don't mind explicitly referring to the base table for write operations and not being able to use RETURNING. I've been looking at write support primarily because I was under the impression from prior discussion I read that the feature wasn't considered committable as a read-only feature. If a consensus can be built that read-only RLS would be acceptable after all, then I'll happily defer that in favour of the other work items. -- Craig Ringer 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] Problem with displaying "wide" tables in psql
On Wed, Dec 11, 2013 at 11:13 PM, Sergey Muraviov wrote: > Hi. > > I've improved the patch. > It works in expanded mode when either format option is set to wrapped (\pset > format wrapped), or we have no pager, or pager doesn't chop long lines (so > you can still use the trick). > Target output width is taken from either columns option (\pset columns 70), > or environment variable $COLUMNS, or terminal size. > And it's also compatible with any border style (\pset border 0|1|2). > > Here are some examples: > > postgres=# \x 1 > postgres=# \pset format wrapped > postgres=# \pset border 0 > postgres=# select * from wide_table; > * Record 1 > value afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa df > sadfsadfa > sd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f sadf sad fadsf > * Record 2 > value afadsafasd fasdf asdfasd > > postgres=# \pset border 1 > postgres=# \pset columns 70 > postgres=# select * from wide_table; > -[ RECORD 1 ]- > value | afadsafasd fasdf asdfasd fsad fas df sadf sad f sadf sadf sa > | df sadfsadfasd fsad fsa df sadf asd fa sfd sadfsadf asdf sad f > | sadf sad fadsf > -[ RECORD 2 ]- > value | afadsafasd fasdf asdfasd > > postgres=# \pset border 2 > postgres=# \pset columns 60 > postgres=# select * from wide_table; > +-[ RECORD 1 ]-+ > | value | afadsafasd fasdf asdfasd fsad fas df sadf sad f | > | | sadf sadf sa df sadfsadfasd fsad fsa df sadf as | > | | d fa sfd sadfsadf asdf sad f sadf sad fadsf | > +-[ RECORD 2 ]-+ > | value | afadsafasd fasdf asdfasd | > +---+--+ > > Regards, > Sergey > The patch applies and compile cleanly. I tried the following \pset format wrapped \pset columns 70. Not in expanded mode select * from wide_table works fine. select * from pg_stats has problems in viewing. Is it that pg_stats can be viewed easily only in expanded mode i.e. if columns displayed are wrapped then there is no way to view results in non expanded mode? regards Sameer -- 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] Logging WAL when updating hintbit
On Wed, Dec 18, 2013 at 11:22 AM, Amit Kapila wrote: > On Fri, Dec 13, 2013 at 7:57 PM, Heikki Linnakangas > wrote: >> Thanks, committed with some minor changes: > > Should this patch in CF app be moved to Committed Patches or is there > something left for this patch? Nothing has been forgotten for this patch. It can be marked as committed. -- Michael -- 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] [bug fix] pg_ctl always uses the same event source
On Tue, Dec 17, 2013 at 5:33 PM, MauMau wrote:
> From: "Amit Kapila"
>
>> Few minor things:
>
> event_source here is a global static char array, so it's automatically
> initialized with zeros and safe to access.
Right, I had missed that point.
>
>
>> 2. minor coding style issue
>
>
> Thanks. I passed the source files through pgindent and attached the revised
> patch. Although the arguments in the second line are not in line with the
> first line's arguments, that's what pgindent found good.
Okay, no problem.
Few other points:
-
1.
#ifdef WIN32
/* Get event source from postgresql.conf for eventlog output */
get_config_value("event_source", event_source, sizeof(event_source));
#endif
event logging is done for both win32 and cygwin env.
under hash define (Win32 || cygwin),
so event source name should also be retrieved for both
environments. Refer below in code:
#if defined(WIN32) || defined(__CYGWIN__)
static void
write_eventlog(int level, const char *line)
2.
Docs needs to be updated for default value:
http://www.postgresql.org/docs/devel/static/event-log-registration.html
http://www.postgresql.org/docs/devel/static/runtime-config-logging.html#GUC-EVENT-SOURCE
In this patch, we are planing to change default value of event_source
from PostgreSQL to PostgreSQL 9.4 (PostgreSQL PG_MAJORVERSION)
as part of fixing the issue reported in this thread.
If anyone has objection to that, please let us know now to avoid re-work
at later stage.
With Regards,
Amit Kapila.
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] Logging WAL when updating hintbit
On Fri, Dec 13, 2013 at 7:57 PM, Heikki Linnakangas wrote: > On 12/13/2013 07:55 AM, Sawada Masahiko wrote: >> >> On Fri, Dec 13, 2013 at 1:51 PM, Dilip kumar >> wrote: >>> >>> On 04 December 2013, Sawada Masahiko Wrote >>> >> I have modified the patch base on your comment, and I attached the v7 >> patch. > > > Thanks, committed with some minor changes: Should this patch in CF app be moved to Committed Patches or is there something left for this patch? >> I'm not totally satisfied with the name of the GUC, wal_log_hintbits. > Me either; at the very least, it's short an underscore: wal_log_hint_bits > would be more readable. But how about just "wal_log_hints"? +1 for wal_log_hints, it sounds better. With Regards, Amit Kapila. 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] Optimize kernel readahead using buffer access strategy
(2013/12/17 21:29), Simon Riggs wrote: These are interesting results. Good research. Thanks! They also show that the benefit of this is very specific to the exact task being performed. I can't see any future for a setting that applies to everything or nothing. We must be more selective. This patch is still needed some human judgement whether readahead is on or off. But it might have been already useful for clever users. However, I'd like to implement adding more the minimum optimization. We also need much better benchmark results, clearly laid out, so they can be reproduced and discussed. I think this feature is big benefit for OLTP, and it might useful for BI now. BI queries are mostly compicated, so we will need to test more in some situations. Printf debug is very useful for debugging my patch, and it will accelerate the optimization. Please keep working on this. OK. I do it patiently. Regards, -- Mitsumasa KONDO NTT Open Source Software Center -- 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] pg_rewarm status
(2013/12/18 5:33), Robert Haas wrote: Sounds like it might be worth dusting the patch off again... I'd like to request you to add all_index option and usage_count option. When all_index option is selected, all index become rewarm nevertheless user doesn't input relation name. And usage_count option adds usage_copunt in shared_buffers. Useful buffers will remain long and not to be thrown easly. I think these are easy to implements and useful. So please if you like. Regards, -- Mitsumasa KONDO NTT Open Source Software Center -- 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] Doc fix for VACUUM FREEZE
(now with patch--sorry about that) diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml index eb71581..5f03343 100644 --- a/doc/src/sgml/ref/vacuum.sgml +++ b/doc/src/sgml/ref/vacuum.sgml @@ -100,8 +100,9 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ Selects aggressive freezing of tuples. Specifying FREEZE is equivalent to performing - VACUUM with the - parameter + VACUUM with both + and + set to zero. -- 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] Doc fix for VACUUM FREEZE
VACUUM FREEZE sets both vacuum_freeze_min_age and vacuum_freeze_table_age to 0, but only the former is documented. This patch notes that the other setting is also affected.
Re: [HACKERS] [PATCH] SQL assertions prototype
On 12/17/2013 01:42 PM, Kevin Grittner wrote: > Josh Berkus wrote: >> Going back over this patch, I haven't seen any further discussion of the >> point Heikki raises above, which seems like a bit of a showstopper. >> >> Heikki, did you have specific ideas on how to solve this? Right now my >> mind boggles. > > It works fine as long as you set default_transaction_isolation = > 'serializable' and never override that. :-) Of course, it sure > would be nice to have a way to prohibit overrides, but that's > another issue. > > Otherwise it is hard to see how to make it work in a general way > without a mutually exclusive lock mode on the table for the > duration of any transaction which modifies the table. Serializable or not, *what* do we lock for assertions? It's not rows. Tables? Which tables? What if the assertion is an interpreted language function? Does the SSI reference counter really take care of all of 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: [HACKERS] Extension Templates S03E11
On 17 December 2013 23:42, Tom Lane wrote: >> We aim to have the simplest implementation that meets the stated need >> and reasonable extrapolations of that. Text in a catalog table is the >> simplest implementation. That is not a reason to reject it, especially >> when we aren't suggesting a viable alternative. > > The first part of this assertion is debatable, and the claim that no > viable alternative has been suggested is outright wrong. I just hadn't read about that myself. All I'd read was why this feature should be blocked. Sounds like we have a way forward for this feature then, just not with the current patch. Can someone attempt to summarise the way forward, with any caveats and necessary restrictions? It would save further column inches of debate. -- 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] commit fest 2013-11 final report
On 12/17/13, 10:19 AM, Tom Lane wrote: > Perhaps we should just move all the Needs Review and RFC patches forward > to the next fest, so we don't forget about them? This was done the last few times, but it has caused some controversy. One problem was that a number of patches arrived in this commit fest without either the author or the reviewers knowing about it, which caused the already somewhat stale patch to become completely abandoned. I think what I'll do is send an email to each of the affected patch threads describing the situation. But I'd like someone involved in the patch, either author or reviewer, to make the final call about moving the patch forward. -- 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 Templates S03E11
Simon Riggs writes: > On 17 December 2013 18:32, Stephen Frost wrote: >> I have no idea where you're going with this, but I *do* object to >> sticking an SQL script which defines a bunch of objects into a catalog >> table *right next to where they are properly defined*. There's just no >> sense in it that I can see, except that it happens to mimic what we do >> today- to no particular purpose. > The purpose is clear: so it is part of the database backup. It's a > fairly boring purpose, not fancy at all. But it is a purpose, indeed > *the* purpose. The point Stephen is making is that it's just as easy, and far more reliable, to dump the package-or-whatever-you-call-it by dumping the definitions of the contained objects, as to dump it by dumping the text blob it was originally created from. So I don't see a lot of merit to claiming that we need to keep the text blob for this purpose. We did it differently for extensions in part because you can't dump a .so as a SQL command, so dump-the-contained-objects wasn't going to be a complete backup strategy in any case. But for a package containing only SQL objects, that's not a problem. > We aim to have the simplest implementation that meets the stated need > and reasonable extrapolations of that. Text in a catalog table is the > simplest implementation. That is not a reason to reject it, especially > when we aren't suggesting a viable alternative. The first part of this assertion is debatable, and the claim that no viable alternative has been suggested is outright wrong. 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 Templates S03E11
On 17 December 2013 18:32, Stephen Frost wrote: > * Simon Riggs (si...@2ndquadrant.com) wrote: >> I keep seeing people repeat "I don't like blobs" as if that were an >> objection. There is no danger or damage from doing this. I can't see >> any higher beauty that we're striving for by holding out. Why not >> allow the user to choose XML, JSON, YAML, or whatever they choose. > > I have no idea where you're going with this, but I *do* object to > sticking an SQL script which defines a bunch of objects into a catalog > table *right next to where they are properly defined*. There's just no > sense in it that I can see, except that it happens to mimic what we do > today- to no particular purpose. The purpose is clear: so it is part of the database backup. It's a fairly boring purpose, not fancy at all. But it is a purpose, indeed *the* purpose. I don't see any technical objection here. We aim to have the simplest implementation that meets the stated need and reasonable extrapolations of that. Text in a catalog table is the simplest implementation. That is not a reason to reject it, especially when we aren't suggesting a viable alternative. I have zero attachment to this design, my interest is in the feature. How do we achieve the feature if not this way? -- 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] Extension Templates S03E11
On 17 December 2013 17:54, Tom Lane wrote: >> So now, you don't need anymore to have file system write privileges into >> a central place owned by root, it can be anywhere else, > > Modern OSes have security checks that can prevent loading libraries from > random places. This is widely seen as not merely a good thing, but > security-critical for network-exposed daemons. Of which we are one. > > I keep telling you this, and it keeps not sinking in. One more time: any > feature that does what you want will be dead on arrival so far as vendors > like Red Hat are concerned. I don't care how creatively you argue for it, > they will refuse to ship it (or at least refuse to disable the SELinux > policy that prevents it). Period. Please stop wasting my time with > suggestions otherwise, because it won't happen. > > So what we have left to discuss is whether we want to develop, and base a > community extension-distribution infrastructure on, a mechanism that some > popular vendors will actively block. I'm inclined to think it's a bad > idea, but I just work here. Yes, there is a strong argument against enabling Postgres out-of-the-box to allow loading of .so files from random places and bypassing distro security procedures. But that argument doesn't apply to all types of extension. For example, data. In any case, right now, its easy to load an FDW and then do an INSERT SELECT from a foreign server into a text column. There are no restrictions on URLs to access foreign servers. Then write a *trusted* PL/pgSQL procedure to execute the contents of the text column to do whatever. All you need is the Postgres foreign data wrapper loaded, an insecure URL and a route to it. I don't see a material difference between that route and the new one proposed. The only difference is really that the new route would be blessed as being the officially recommended way to import objects without needing a file, and to allow them to be backed up and restored. So perhaps all we need is a module that once loaded allows other things to be loaded. (Red Hat etc can then ban that as they see fit.) -- 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] [PATCH] SQL assertions prototype
On 18/12/13 10:48, Andrew Dunstan wrote: On 12/17/2013 04:42 PM, Kevin Grittner wrote: Josh Berkus wrote: On 11/15/2013 05:41 AM, Heikki Linnakangas wrote: A fundamental problem with this is that it needs to handle isolation reliable, so that the assertion cannot be violated when two concurrent backends do things. Consider the example from the manual, which checks that a table has at least one row. Now, if the table has two rows to begin with, and in one backend you delete one row, and concurrently in another backend you delete the other row, and then commit both transactions, the assertion is violated. In other words, the assertions need to be checked in serializable mode. Now that we have a real serializable mode, I think that's actually feasible. Going back over this patch, I haven't seen any further discussion of the point Heikki raises above, which seems like a bit of a showstopper. Heikki, did you have specific ideas on how to solve this? Right now my mind boggles. It works fine as long as you set default_transaction_isolation = 'serializable' and never override that. :-) Of course, it sure would be nice to have a way to prohibit overrides, but that's another issue. Otherwise it is hard to see how to make it work in a general way without a mutually exclusive lock mode on the table for the duration of any transaction which modifies the table. Maybe the presence of an assertion should be enough to force serializable, i.e. turn it on and not allow it to be turned off. cheers andrew Perhaps then it should be called an 'assurance', rather than an 'assertion?' (Not being entirely facetious!) Cheers, Gavin -- 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] SQL assertions prototype
On 12/17/2013 04:42 PM, Kevin Grittner wrote: Josh Berkus wrote: On 11/15/2013 05:41 AM, Heikki Linnakangas wrote: A fundamental problem with this is that it needs to handle isolation reliable, so that the assertion cannot be violated when two concurrent backends do things. Consider the example from the manual, which checks that a table has at least one row. Now, if the table has two rows to begin with, and in one backend you delete one row, and concurrently in another backend you delete the other row, and then commit both transactions, the assertion is violated. In other words, the assertions need to be checked in serializable mode. Now that we have a real serializable mode, I think that's actually feasible. Going back over this patch, I haven't seen any further discussion of the point Heikki raises above, which seems like a bit of a showstopper. Heikki, did you have specific ideas on how to solve this? Right now my mind boggles. It works fine as long as you set default_transaction_isolation = 'serializable' and never override that. :-) Of course, it sure would be nice to have a way to prohibit overrides, but that's another issue. Otherwise it is hard to see how to make it work in a general way without a mutually exclusive lock mode on the table for the duration of any transaction which modifies the table. Maybe the presence of an assertion should be enough to force serializable, i.e. turn it on and not allow it to be turned off. 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] [PATCH] SQL assertions prototype
Josh Berkus wrote: > On 11/15/2013 05:41 AM, Heikki Linnakangas wrote: >> A fundamental problem with this is that it needs to handle isolation >> reliable, so that the assertion cannot be violated when two concurrent >> backends do things. Consider the example from the manual, which checks >> that a table has at least one row. Now, if the table has two rows to >> begin with, and in one backend you delete one row, and concurrently in >> another backend you delete the other row, and then commit both >> transactions, the assertion is violated. >> >> In other words, the assertions need to be checked in serializable mode. >> Now that we have a real serializable mode, I think that's actually >> feasible. > > Going back over this patch, I haven't seen any further discussion of the > point Heikki raises above, which seems like a bit of a showstopper. > > Heikki, did you have specific ideas on how to solve this? Right now my > mind boggles. It works fine as long as you set default_transaction_isolation = 'serializable' and never override that. :-) Of course, it sure would be nice to have a way to prohibit overrides, but that's another issue. Otherwise it is hard to see how to make it work in a general way without a mutually exclusive lock mode on the table for the duration of any transaction which modifies the table. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [bug fix] multibyte messages are displayed incorrectly on the client
On Fri, Dec 13, 2013 at 10:41:17PM +0900, MauMau wrote: > [Cause] > While the session is being established, the server cannot use the > client encoding for message conversion yet, because it cannot access > system catalogs to retrieve conversion functions. So, the server > sends messages to the client without conversion. In the above > example, the server sends Japanese UTF-8 messages to psql, which > expects those messages in SJIS. > > > [Fix] > Disable message localization during session startup. In other > words, messages are output in English until the database session is > established. I think the question is whether the server encoding or English are likely to be better for the average client. My bet is that the server encoding is more likely correct. However, you are right that English/ASCII at least will always be viewable, while there are many server/client combinations that will produce unreadable characters. I would be interested to hear other people's experience with this. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] 9.3 reference constraint regression
Alvaro Herrera wrote:
> Well, it would help if those cases weren't dead code. Neither
> heap_update nor heap_delete are ever called in the "no wait" case at
> all. Only heap_lock_tuple is, and I can't see any misbehavior there
> either, even with HeapTupleBeingUpdated returned when there's a
> non-local locker, or when there's a MultiXact as xmax, regardless of its
> status.
I spent some more time trying to generate a test case that would show a
problem with the changed return values here, and was unable to.
I intend to apply this patch soon.
--
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
*** a/src/backend/utils/time/tqual.c
--- b/src/backend/utils/time/tqual.c
***
*** 686,693 HeapTupleSatisfiesUpdate(HeapTupleHeader tuple, CommandId curcid,
if (tuple->t_infomask & HEAP_XMAX_INVALID) /* xid invalid */
return HeapTupleMayBeUpdated;
! if (HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask)) /* not deleter */
return HeapTupleMayBeUpdated;
if (tuple->t_infomask & HEAP_XMAX_IS_MULTI)
{
--- 686,713
if (tuple->t_infomask & HEAP_XMAX_INVALID) /* xid invalid */
return HeapTupleMayBeUpdated;
! if (HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask))
! {
! /*
! * Careful here: even if this tuple was created by our
! * transaction, it might be locked by other transactions, in
! * case the original version was key-share locked when we
! * updated it. We cannot simply return MayBeUpdated, because
! * that would lead to those locks being ignored in the future.
! * Therefore we return HeapTupleBeingUpdated here, which
! * causes the caller to recheck those locks.
! *
! * Note we might return BeingUpdated spuriously in some cases,
! * particularly when there's a multixact which has no members
! * outside of this transaction. This doesn't cause any issues
! * currently, but might need tweaking.
! */
! if (tuple->t_infomask & HEAP_XMAX_IS_MULTI)
! return HeapTupleBeingUpdated;
! else if (!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple)))
! return HeapTupleBeingUpdated;
return HeapTupleMayBeUpdated;
+ }
if (tuple->t_infomask & HEAP_XMAX_IS_MULTI)
{
***
*** 700,706 HeapTupleSatisfiesUpdate(HeapTupleHeader tuple, CommandId curcid,
/* updating subtransaction must have aborted */
if (!TransactionIdIsCurrentTransactionId(xmax))
! return HeapTupleMayBeUpdated;
else
{
if (HeapTupleHeaderGetCmax(tuple) >= curcid)
--- 720,733
/* updating subtransaction must have aborted */
if (!TransactionIdIsCurrentTransactionId(xmax))
! {
! /*
! * This would normally be HeapTupleMayBeUpdated, but
! * we do this instead to cause caller to recheck
! * other lockers; see note above in the LOCKED_ONLY case.
! */
! return HeapTupleBeingUpdated;
! }
else
{
if (HeapTupleHeaderGetCmax(tuple) >= curcid)
--
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] SQL assertions prototype
On 11/15/2013 05:41 AM, Heikki Linnakangas wrote: > A fundamental problem with this is that it needs to handle isolation > reliable, so that the assertion cannot be violated when two concurrent > backends do things. Consider the example from the manual, which checks > that a table has at least one row. Now, if the table has two rows to > begin with, and in one backend you delete one row, and concurrently in > another backend you delete the other row, and then commit both > transactions, the assertion is violated. > > In other words, the assertions need to be checked in serializable mode. > Now that we have a real serializable mode, I think that's actually > feasible. Going back over this patch, I haven't seen any further discussion of the point Heikki raises above, which seems like a bit of a showstopper. Heikki, did you have specific ideas on how to solve this? Right now my mind boggles. -- 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] planner missing a trick for foreign tables w/OR conditions
Simon Riggs writes: > What surprises me is we don't have an API that allows an FDW to decide > what it can accept or not. It seems strange to have a unilateral > decision by our planner about what another planner is capable of. Uh, what? There's certainly missing features in our FDW APIs --- no ability to push over joins or aggregates for instance --- but none of that has anything to do with assumptions about what the other end is capable of. We're just not done inventing those APIs. 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] planner missing a trick for foreign tables w/OR conditions
Robert Haas writes: > On Tue, Dec 17, 2013 at 12:28 PM, Tom Lane wrote: >> (I wonder if it'd be worth inserting a check that >> there's not already a manually-generated equivalent clause, too ...) > Sounds a little too clever IMHO. The argument for doing it is that we might otherwise find ourselves degrading the plans for previously-manually-optimized queries. On the other hand, the existing index-driven code has probably forestalled the need for many people to do that; at least, I don't recall seeing much discussion of doing that sort of thing by hand. I'm happy to leave the issue out of the first version of the patch, anyway. 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] planner missing a trick for foreign tables w/OR conditions
On 17 December 2013 17:28, Tom Lane wrote: > So at this point I'm pretty much talked into it. We could eliminate the > dependence on indexes entirely, and replace this code with a step that > simply tries to pull single-base-relation quals out of ORs wherever it can > find one. You could argue that the produced quals would sometimes not be > worth testing for, but we could apply a heuristic that says to forget it > unless the estimated selectivity of the extracted qual is less than, > I dunno, 0.5 maybe. (I wonder if it'd be worth inserting a check that > there's not already a manually-generated equivalent clause, too ...) Sounds sensible. What surprises me is we don't have an API that allows an FDW to decide what it can accept or not. It seems strange to have a unilateral decision by our planner about what another planner is capable of. Should we extend the API to allow the question to be asked? -- 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] SSL: better default ciphersuite
On Sun, Dec 15, 2013 at 05:10:38PM -0500, James Cloos wrote: > > "MK" == Marko Kreen writes: > > "PE" == Peter Eisentraut writes: > PE> Any other opinions on this out there? > > For reference, see: > > https://wiki.mozilla.org/Security/Server_Side_TLS > > for the currently suggested suite for TLS servers. > > That is: > > ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES128-GCM-SHA256: > ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES256-GCM-SHA384: > DHE-RSA-AES128-GCM-SHA256:DHE-DSS-AES128-GCM-SHA256:kEDH+AESGCM: > ECDHE-RSA-AES128-SHA256:ECDHE-ECDSA-AES128-SHA256:ECDHE-RSA-AES128-SHA: > ECDHE-ECDSA-AES128-SHA:ECDHE-RSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA384: > ECDHE-RSA-AES256-SHA:ECDHE-ECDSA-AES256-SHA:DHE-RSA-AES128-SHA256: > DHE-RSA-AES128-SHA:DHE-DSS-AES128-SHA256:DHE-RSA-AES256-SHA256: > DHE-DSS-AES256-SHA:DHE-RSA-AES256-SHA:AES128-GCM-SHA256: > AES256-GCM-SHA384:ECDHE-RSA-RC4-SHA:ECDHE-ECDSA-RC4-SHA: > AES128:AES256:RC4-SHA:HIGH: > !aNULL:!eNULL:!EXPORT:!DES:!3DES:!MD5:!PSK This is example of ciphersuite list for people who have special requirements and care about tracking yearly changes in SSL landscape. And can deploy config changes relatively fast. This discussion is about Postgres default suite which cannot and should not be periodically changed, for people who leave Postgres settings to defaults and expect setup work well. We would like to leave as much as possible to OpenSSL, but not more. Looking at the history of OpenSSL, their default order has been good, except the 3DES vs. AES128 priority. Looking into future, I guess following events are likely: - RC4 gets practially broken and/or removed from TLS (draft-popov-tls-prohibiting-rc4-01). - New ciphersuites: Salsa/Chacha (256-bit key). - New modes: CCM (RFC6655, draft-mcgrew-tls-aes-ccm-ecc-07), other ciphers with GCM, new AEAD constructs. - CBC mode fixes: pad-mac-encrypt, pad-encrypt-mac. Those may be implemented with TLS extensions, so no new ciphersuites. RC4 situation - the 'MEDIUM' in my proposal communicates that not all ciphers are best, and prefer-server-order makes sure it is selected as last resort. So that is solved. New ciphersuites - if we want to select fastest from "secure" suites we need to change configuration periodically (RC4->AES128-CBC->AES128-GCM->SALSA) and I don't think Postgres should bother we that. So I think it's better to leave ordering new ciphers to OpenSSL, and people who have special requirements can worry about best configuration for specific stack they are running. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GiST support for inet datatypes
Hi, Attached patch adds GiST support to the inet datatypes with two new operators. Overlaps operator can be used with exclusion constraints. Is adjacent to operator is just the negator of it. Index uses only the network bits of the addresses. Except for the new operators and is contained within, contains; basic comparison operators are also supported. Query planner never chooses to use the index for the operators which the index is particularly useful because selectivity estimation functions are missing. I am planning to work on them. I also wanted to add strictly left of and strictly right of operators but I did not want to introduce new symbols. I think we need style guidelines for them. Range types use <@ and @> for is contained within and contains operators; << and >> for strictly left of and strictly right of operators. It would be nice if we could change the symbols for contains and is contained within operators of the inet datatypes. Then we could use the old ones for strictly left of and strictly right of operators. I did not touch opr_sanity regression tests as I did not decide how to solve these problems. I did not add documentation except the new operators. It would be nice to mention the index and exclusion constraints for inet datatypes somewhere. I did not know which page would be more suitable. inet-gist-v1.patch Description: Binary data -- 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] pg_rewarm status
From: "Robert Haas" On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila wrote: I have used pg_prewarm during some of work related to Buffer Management and other performance related work. It is quite useful utility. +1 for reviving this patch for 9.4 Any other votes? +1 Some customers requested: 1. fill the database cache with frequently accessed data before starting or resuming service for their users (for the first time or after maintenance work), so that they can provide steady and predictable performance. 2. pin some (reference or master) data in the database cache not to be evicted from the cache (like Oracle's KEEP buffer?), for the same reason as 1. I'd love such useful feature like pg_rewarm to be included in core. I hope such nice features won't be rejected just because there are already similar external tools. Regards MauMau -- 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] pg_rewarm status
On 12/17/2013 06:34 AM, Robert Haas wrote: > On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila wrote: >> I have used pg_prewarm during some of work related to Buffer Management and >> other performance related work. It is quite useful utility. >> +1 for reviving this patch for 9.4 > > Any other votes? I still support this patch (as I did originally), and don't think that the overlap with pgFincore is of any consequence. pgFincore does more than pgrewarm ever will, but it's also platform-specific, so it still makes sense for both to exist. -- 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] SSL: better default ciphersuite
On 18/12/13 05:26, Bruce Momjian wrote: On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote: On Sun, Dec 15, 2013 at 5:10 PM, James Cloos wrote: For reference, see: https://wiki.mozilla.org/Security/Server_Side_TLS for the currently suggested suite for TLS servers. ... But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful for some. And RC4, perhaps, also should be !ed. And if anyone wants Kerberos tls-authentication, one could add KRB5-DES-CBC3-SHA, but that is ssl3-only. Once salsa20-poly1305 lands in openssl, that should be added to the start of the list. I'm starting to think we should just leave this well enough alone. We can't seem to find two people with the same idea of what would be better than what we have now. And of course the point of making it a setting in the first place is that each person can set it to whatever they deem best. Yes, I am seeing that too. Can we agree on one that is _better_ than what we have now, even if we can't agree on a _best_ one? Because various security agencies probably have people trying to confuse the issue, and acting to discourage strong encryption... Possibly choose the one computationally most difficult to crack - but even then, we don't know what algorithms they are using, which are bound to be very sophisticated. I've a horrible feeling, that I'm not paranoid enough! Cheers, Gavin -- 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] SSL: better default ciphersuite
On Tue, Dec 17, 2013 at 11:26:13AM -0500, Bruce Momjian wrote: > On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote: > > I'm starting to think we should just leave this well enough alone. We > > can't seem to find two people with the same idea of what would be > > better than what we have now. And of course the point of making it a > > setting in the first place is that each person can set it to whatever > > they deem best. > > Yes, I am seeing that too. Can we agree on one that is _better_ than > what we have now, even if we can't agree on a _best_ one? To recap - old settings are: DEFAULT:!LOW:!EXP:!MD5:@STRENGTH prefer-client-order new proposal is: HIGH:MEDIUM:+3DES:!aNULL prefer-server-order This is better than old state in following aspects: - First, it does not remove any ciphers compared to current list. So anything that could connect previously can connect still. - Clearer to people not intimately familiar with OpenSSL and TLS. In particular, the 'MEDIUM' communicates that some less secure ciphers are enabled (RC4). - Fixes the 3DES ordering. OpenSSL default list is ordered roughly by (key-bits, ECDHE, DHE, plain RSA). 3DES has 168-bit key so it appears before 128-bit ciphers, although it offers around 112-bit actual security. This problem exists already with existing Postgres versions: if you set suite to "AES128:3DES", then libpq-based clients will use 3DES. When combined with prefer-server-order, it has following benefits: - Clarity: admin can look at configured cipher order and make reasonable guesses what will be used. - Actually activates the 3DES fix. Although non-libpq/OpenSSL based clients did used their own order, OpenSSL-based client did have same order problem in client-side. - Old clients that did prefer RC4 will use it as last resort only, when only alternative is 3DES. - Old clients that did prefer non-DHE ciphers will use DHE ciphers if available. One goal the new settings *do not* try to achieve is to pick the absolutely fastest cipher from the secure ones. Old settings did not it either, when speaking of libpq clients. Java did try from client-side, but as a result old deployed versions use now insecure settings. I think it's best when the default settings prefer security over speed, everyone who is has special requirements speed-wise - "AES is slow" - can tune list themselves. So, does anyone have reasons not to use proposed new default? -- 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] pg_rewarm status
On Tue, Dec 17, 2013 at 3:31 PM, MauMau wrote: >> Any other votes? > > +1 > Some customers requested: > > 1. fill the database cache with frequently accessed data before starting or > resuming service for their users (for the first time or after maintenance > work), so that they can provide steady and predictable performance. > > 2. pin some (reference or master) data in the database cache not to be > evicted from the cache (like Oracle's KEEP buffer?), for the same reason as > 1. > > I'd love such useful feature like pg_rewarm to be included in core. I hope > such nice features won't be rejected just because there are already similar > external tools. For the record, the name of the tool is pg_PREwarm, not pg_rewarm. The subject line of this thread is a typo. Sounds like it might be worth dusting the patch off again... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SSL: better default ciphersuite
On 12/17/2013 08:26 AM, Bruce Momjian wrote: On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote: On Sun, Dec 15, 2013 at 5:10 PM, James Cloos wrote: For reference, see: https://wiki.mozilla.org/Security/Server_Side_TLS for the currently suggested suite for TLS servers. ... But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful for some. And RC4, perhaps, also should be !ed. And if anyone wants Kerberos tls-authentication, one could add KRB5-DES-CBC3-SHA, but that is ssl3-only. Once salsa20-poly1305 lands in openssl, that should be added to the start of the list. I'm starting to think we should just leave this well enough alone. We can't seem to find two people with the same idea of what would be better than what we have now. And of course the point of making it a setting in the first place is that each person can set it to whatever they deem best. Yes, I am seeing that too. Can we agree on one that is _better_ than what we have now, even if we can't agree on a _best_ one? Agreed. I would note that what is being proposed is a default that helps those of us (myself included) that do not know ciphers in and out, start with reasonable expectation of protection. This is a GUC so it can be modified to suite personal taste. -- Adrian Klaver adrian.kla...@gmail.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] row security roadmap proposal
On 17 December 2013 17:03, Robert Haas wrote: > On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith > wrote: >> On 12/16/13 9:36 AM, Craig Ringer wrote: >>> >>> - Finish and commit updatable security barrier views. I've still got a >>> lot of straightening out to do there. >> >> I don't follow why you've put this part first. It has a lot of new >> development and the risks that go along with that, but the POC projects I've >> been testing are more interested in the view side issues. > > I don't really see a way that any of this can work without that. To > be clear, that work is required even just for read-side security. Not sure I'd say required, but its certainly desirable to have updateable security barrier views in themselves. And it comes across to me as a cleaner and potentially more performant way of doing the security checks for RLS. So I think its the right thing to do to wait for this, even if we can't do that for 9.4 Realistically, we have a significant amount of work before we're ready to pass a high security audit based around these features. -- 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
[HACKERS] processing time zone
I had to write obscure code for processing time zone and using it for
timestamptz
Datum
make_timestamptz_at_timezone(PG_FUNCTION_ARGS)
{
Timestamp timestamp;
text *zone;
int tz;
chartzname[TZ_STRLEN_MAX + 1];
char *lowzone;
int type,
val;
struct pg_tm tt,
*tm = &tt;
fsec_t fsec;
TimestampTz result;
int session_tz;
timestamp = make_timestamp_internal(PG_GETARG_INT32(0), /* year */
PG_GETARG_INT32(1),/* month */
PG_GETARG_INT32(2),/* mday */
PG_GETARG_INT32(3),/* hour */
PG_GETARG_INT32(4),/* min */
PG_GETARG_FLOAT8(5)); /* sec */
if (timestamp2tm(timestamp, NULL, tm, &fsec, NULL, NULL) != 0)
ereport(ERROR,
(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
errmsg("timestamp out of range")));
zone = PG_GETARG_TEXT_PP(6);
text_to_cstring_buffer(zone, tzname, sizeof(tzname));
if (DecodeTimezone(tzname, &tz) != 0)
{
lowzone = downcase_truncate_identifier(tzname,
strlen(tzname),
false);
type = DecodeSpecial(0, lowzone, &val);
if (type == TZ || type == DTZ)
tz = val * MINS_PER_HOUR;
else
{
pg_tz *tzp;
tzp = pg_tzset(tzname);
if (tzp)
tz = DetermineTimeZoneOffset(tm, tzp);
else
{
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("time zone \"%s\"
not recognized", tzname)));
tz =
0; /* keep compiler quiet */
}
}
}
elog(NOTICE, "entry 0: %d", tz);
session_tz = DetermineTimeZoneOffset(tm, session_timezone);
PG_RETURN_TIMESTAMPTZ((TimestampTz) dt2local(timestamp, -tz));
}
It works
postgres=# select make_timestamptz(2014,12,17,21,06,37.7,'Europe/Moscow') ;
make_timestamptz
--
2014-12-17 18:06:37.7+01
(1 row)
postgres=# select '2014-12-17 21:06:37.7 Europe/Moscow'::timestamptz;
timestamptz
--
2014-12-17 18:06:37.7+01
(1 row)
Is some better way, how to parse time zone?
Regards
Pavel Stehule
Re: [HACKERS] Extension Templates S03E11
On 12/17/2013 08:32 PM, Stephen Frost wrote: * Simon Riggs (si...@2ndquadrant.com) wrote: My only personal interest in this is to stimulate the writing of further extensions, which is fairly clearly hampered by the overhead required for packaging. I'm not convinced of that but I agree that we can do better and would like to see a solution which actually makes progress in that regard. I don't feel that this does that- indeed, it hardly changes the actual packaging effort required of extension authors at all. I'll repeat my requirement: the same extension must be installable the old way and the new way. I've lost track which of the ideas being discussed satisfy that requirement, but I object to any that doesn't. Considering that, I don't see how any if this is going to reduce the overhead required for packaging. An extension author will write the extension exactly the same way he does today. Perhaps you meant the overhead of installing an extension, ie. the work that the DBA does, not the work that the extension author does? - Heikki -- 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] preserving forensic information when we freeze
On Wed, Dec 11, 2013 at 5:25 PM, Alvaro Herrera
wrote:
> Andres Freund escribió:
>> What's your plan to commit this? I'd prefer to wait till Alvaro's
>> freezing changes get in, so his patch will look the same in HEAD and
>> 9.3. But I think he plans to commit soon.
>
> Yes, I do. I'm waiting on feedback on the patch I posted this
> afternoon, so if there's nothing more soon I will push it.
That's done now, so I've rebased this patch and hacked on it a bit
more. The latest version is attached. Review would be appreciate in
case I've goofed up anything critical, especially around adjusting
things over top of Alvaro's freezing changes. But I think this is
more or less ready to go.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
diff --git a/contrib/pageinspect/heapfuncs.c b/contrib/pageinspect/heapfuncs.c
index 6d8f6f1..a78cff3 100644
--- a/contrib/pageinspect/heapfuncs.c
+++ b/contrib/pageinspect/heapfuncs.c
@@ -162,7 +162,7 @@ heap_page_items(PG_FUNCTION_ARGS)
tuphdr = (HeapTupleHeader) PageGetItem(page, id);
- values[4] = UInt32GetDatum(HeapTupleHeaderGetXmin(tuphdr));
+ values[4] = UInt32GetDatum(HeapTupleHeaderGetRawXmin(tuphdr));
values[5] = UInt32GetDatum(HeapTupleHeaderGetRawXmax(tuphdr));
values[6] = UInt32GetDatum(HeapTupleHeaderGetRawCommandId(tuphdr)); /* shared with xvac */
values[7] = PointerGetDatum(&tuphdr->t_ctid);
diff --git a/src/backend/access/common/heaptuple.c b/src/backend/access/common/heaptuple.c
index e39b977..347d616 100644
--- a/src/backend/access/common/heaptuple.c
+++ b/src/backend/access/common/heaptuple.c
@@ -539,7 +539,7 @@ heap_getsysattr(HeapTuple tup, int attnum, TupleDesc tupleDesc, bool *isnull)
result = ObjectIdGetDatum(HeapTupleGetOid(tup));
break;
case MinTransactionIdAttributeNumber:
- result = TransactionIdGetDatum(HeapTupleHeaderGetXmin(tup->t_data));
+ result = TransactionIdGetDatum(HeapTupleHeaderGetRawXmin(tup->t_data));
break;
case MaxTransactionIdAttributeNumber:
result = TransactionIdGetDatum(HeapTupleHeaderGetRawXmax(tup->t_data));
diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c
index db683b1..a9fcd98 100644
--- a/src/backend/access/heap/heapam.c
+++ b/src/backend/access/heap/heapam.c
@@ -1738,7 +1738,7 @@ heap_hot_search_buffer(ItemPointer tid, Relation relation, Buffer buffer,
*/
if (TransactionIdIsValid(prev_xmax) &&
!TransactionIdEquals(prev_xmax,
- HeapTupleHeaderGetXmin(heapTuple->t_data)))
+ HeapTupleHeaderGetRawXmin(heapTuple->t_data)))
break;
/*
@@ -1908,7 +1908,7 @@ heap_get_latest_tid(Relation relation,
* tuple. Check for XMIN match.
*/
if (TransactionIdIsValid(priorXmax) &&
- !TransactionIdEquals(priorXmax, HeapTupleHeaderGetXmin(tp.t_data)))
+ !TransactionIdEquals(priorXmax, HeapTupleHeaderGetRawXmin(tp.t_data)))
{
UnlockReleaseBuffer(buffer);
break;
@@ -2257,13 +2257,10 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid,
tup->t_data->t_infomask &= ~(HEAP_XACT_MASK);
tup->t_data->t_infomask2 &= ~(HEAP2_XACT_MASK);
tup->t_data->t_infomask |= HEAP_XMAX_INVALID;
+ HeapTupleHeaderSetXmin(tup->t_data, xid);
if (options & HEAP_INSERT_FROZEN)
- {
- tup->t_data->t_infomask |= HEAP_XMIN_COMMITTED;
- HeapTupleHeaderSetXmin(tup->t_data, FrozenTransactionId);
- }
- else
- HeapTupleHeaderSetXmin(tup->t_data, xid);
+ HeapTupleHeaderSetXminFrozen(tup->t_data);
+
HeapTupleHeaderSetCmin(tup->t_data, cid);
HeapTupleHeaderSetXmax(tup->t_data, 0); /* for cleanliness */
tup->t_tableOid = RelationGetRelid(relation);
@@ -5094,7 +5091,7 @@ l4:
* the end of the chain, we're done, so return success.
*/
if (TransactionIdIsValid(priorXmax) &&
- !TransactionIdEquals(HeapTupleHeaderGetXmin(mytup.t_data),
+ !TransactionIdEquals(HeapTupleHeaderGetRawXmin(mytup.t_data),
priorXmax))
{
UnlockReleaseBuffer(buf);
@@ -5725,12 +5722,6 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid,
TransactionIdPrecedes(xid, cutoff_xid))
{
frz->frzflags |= XLH_FREEZE_XMIN;
-
- /*
- * Might as well fix the hint bits too; usually XMIN_COMMITTED will
- * already be set here, but there's a small chance not.
- */
- frz->t_infomask |= HEAP_XMIN_COMMITTED;
changed = true;
}
@@ -5837,13 +5828,6 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid,
frz->frzflags |= XLH_INVALID_XVAC;
else
frz->frzflags |= XLH_FREEZE_XVAC;
-
- /*
- * Might as well fix the hint bits too; usually XMIN_COMMITTED
- * will already be set here, but there's a small chance not.
- */
- Assert(!(tuple->t_infomask & HEAP_XMIN_INVALID));
- frz->t_infomask |= HEAP_XMIN_COMMITTED;
changed = true;
}
}
@@ -5874,19 +5858,27 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid,
void
heap_execute_freeze_tuple(HeapTupleHeader tuple, xl_heap
Re: [HACKERS] Extension Templates S03E11
* Simon Riggs (si...@2ndquadrant.com) wrote: > I keep seeing people repeat "I don't like blobs" as if that were an > objection. There is no danger or damage from doing this. I can't see > any higher beauty that we're striving for by holding out. Why not > allow the user to choose XML, JSON, YAML, or whatever they choose. I have no idea where you're going with this, but I *do* object to sticking an SQL script which defines a bunch of objects into a catalog table *right next to where they are properly defined*. There's just no sense in it that I can see, except that it happens to mimic what we do today- to no particular purpose. > Blocking this stops nothing, it just forces people to do an extra > non-standard backflip to achieve their goals. Is that what we want? > Why? It's hardly non-standard when it's required for 80+% of the extensions that exist today anyway. > That is clear evidence that the packaging is getting in the way of > extensions that don't include binary programs. I'm totally on-board with coming up with a solution for extensions which do not include .so's. Avoiding mention of the .so issue doesn't somehow change this solution into one which actually solves the issue around non-binary extensions. > My only personal interest in this is to stimulate the writing of > further extensions, which is fairly clearly hampered by the overhead > required for packaging. I'm not convinced of that but I agree that we can do better and would like to see a solution which actually makes progress in that regard. I don't feel that this does that- indeed, it hardly changes the actual packaging effort required of extension authors at all. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] row security roadmap proposal
On 16 December 2013 14:36, Craig Ringer wrote: > - Decide on and implement a structure for row-security functionality its > self. I'm persuaded by Robert's comments here, that whatever we expose > must be significantly more usable than a DIY on top of views (with the > fix for updatable security barrier views to make that possible). I > outlined the skeleton of a possible design in my earlier post, with the > heirachical and non-heirachical access labels. Should be implemented > using the same API we expose for extensions (like SEPostgresql RLS). That part isn't clear why we "must" do better than that. Having declarative security is a huge step forward, in just the same way that updateable views were. They save the need for writing scripts to implement things, rather than just having a useful default. If there is a vision for that, lets see the vision. And then decide whether its worth waiting for. Personally, I see no reason not to commit the syntax we have now. So people can see what we'll be supporting, whenever that is. -- 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] Extension Templates S03E11
On 13 December 2013 18:42, Stephen Frost wrote: > * Jeff Davis (pg...@j-davis.com) wrote: >> For what it's worth, I think the idea of extension templates has good >> conceptual integrity. Extensions are external blobs. To make them work >> more smoothly in several ways, we move them into the catalog. They have >> pretty much the same upsides and downsides of our existing extensions, >> aside from issues directly related to filesystem vs. catalog. > > I've never particularly liked the idea that extensions are external > blobs, to be honest. I've been reading this, trying to catch back up with hackers. This thread is amazing because this feature ought to be a shoe-in. Jeff expresses his points politely, but not strongly enough. I agree with him. I keep seeing people repeat "I don't like blobs" as if that were an objection. There is no danger or damage from doing this. I can't see any higher beauty that we're striving for by holding out. Why not allow the user to choose XML, JSON, YAML, or whatever they choose. Some things need to wait for the right design, like RLS, for a variety of reasons. I don't see any comparison here and I can't see any reason for a claim of veto on grounds of higher wisdom to apply to this case. Blocking this stops nothing, it just forces people to do an extra non-standard backflip to achieve their goals. Is that what we want? Why? >> Stephen had some legitimate concerns. I don't entirely agree, but they >> are legitimate concerns, and we don't want to just override them. >> >> At the same time, I'm skeptical of the alternatives Stephen offered >> (though I don't think he intended them as a full proposal). > > It was more thoughts on how I'd expect these things to work. I've also > been talking to David about what he'd like to see done with PGXN and his > thinking was a way to automate creating RPMs and DEBs based on PGXN spec > files, but he points out that the challenge there is dealing with > external dependencies. > >> So right now I'm discouraged about the whole idea of installing >> extensions using SQL. I don't see a lot of great options. On top of >> that, the inability to handle native code limits the number of >> extensions that could make use of such a facility, which dampens my >> enthusiasm. > > Yeah, having looked at PGXN, it turns out that some 80+% of the > extensions there have .c code included, something well beyond what I was > expecting, but most of those cases also look to have external > dependencies (eg: FDWs), which really makes me doubt this notion that > they could be distributed independently and outside of the OS packaging > system (or that it would be a particularly good idea to even try...). That is clear evidence that the packaging is getting in the way of extensions that don't include binary programs. My only personal interest in this is to stimulate the writing of further extensions, which is fairly clearly hampered by the overhead required for packaging. Who needs old fashioned package management? Some bigger extensions need it. Smaller ones don't. Who are we to force people to distribute their wares in only certain ways? I can't see any reason to block this, nor any better design than the flexible, neutral and simple one proposed. If there's some secret reason to block this, please let me know off list cos I currently don't get it at all. -- 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] Extension Templates S03E11
On 12/16/2013 11:44 AM, Tom Lane wrote:> Right. I think a lot of the tension comes from people being unconvinced > that the existing extension feature is an ideal model for this sort of > use-case. Extensions were mainly designed around the notion of a .so > with some SQL datatype/function/etc declarations that have to match up > with the C code. So it made sense for them to be relatively static things > that live in the filesystem. Notably, if you're migrating to a new PG > major version, you're at the very least gonna have to recompile the C code > and quite likely will need to change it some. So adapting the SQL code > if needed goes along with that, and would never be automatic in any case. I see what you mean. On the other hand: a) introducing a new concept would require a new reserved word b) it would also require figuring out how it interacts with extensions c) extensions already have versioning, which this feature needs d) extensions already have dependancies, which this feature needs While it splits Extensions into two slightly different concepts, I find that on the whole less confusing than the alternative. On 12/16/2013 05:17 PM, Jim Nasby wrote: > Somewhat related to this, I really wish Postgres had the idea of a > "class", that was allowed to contain any type of object and could be > "instantiated" when needed. For example, if we had an "address class", > we could instantiate it once for tracking our customer addresses, and a > second time for tracking the addresses customers supply for their > employers. Such a mechanism would probably be ideal for what we need, > but of course you'd still have the question of how to load a class > definition that someone else has published. Well, the idea originally (POSTGRES) was for the Type, Domain, and Inheritance system to do just what you propose. Nobody ever worked out all the practicalities and gotchas to make it really work in production, though. -- 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] planner missing a trick for foreign tables w/OR conditions
On Tue, Dec 17, 2013 at 12:28 PM, Tom Lane wrote: > I had been thinking it would fall down if there are several OR conditions > affecting different collections of rels, but after going through the math > again, I'm now thinking I was wrong and it does in fact work out. As you > say, we do depend on all paths generating the same rows, but since the > extracted single-rel quals are inserted as plain baserestrictinfo quals, > that'll be true. OK. > A bigger potential objection is that we're opening ourselves to larger > problems with estimation failures due to correlated qual conditions, but > again I'm finding that the math doesn't bear that out. It's reasonable > to assume that our estimate for the extracted qual will be better than > our estimate for the OR as a whole, so our adjusted size estimates for > the filtered base relations are probably OK. And the adjustment to the > OR clause selectivity means that the size estimate for the join comes > out exactly the same. We'll actually be better off than with what is > likely to happen now, which is that people manually extract the simplified > condition and insert it into the query explicitly. PG doesn't realize > that that's redundant and so will underestimate the join size. I had not thought of that, but it seems like a valid point. > So at this point I'm pretty much talked into it. We could eliminate the > dependence on indexes entirely, and replace this code with a step that > simply tries to pull single-base-relation quals out of ORs wherever it can > find one. You could argue that the produced quals would sometimes not be > worth testing for, but we could apply a heuristic that says to forget it > unless the estimated selectivity of the extracted qual is less than, > I dunno, 0.5 maybe. This is an area where I think things are very different from local and remote tables. For a local table, the cost of transmitting a row from one plan node to another is basically zero. For a remote table, it's potentially far higher, although unfortunately it's hard to know exactly. But if the qual is cheap to evaluate, and we're getting back a lot of rows, I suspect even eliminating 5-10% of them could work out to a win. With a local table, 50% sounds like a reasonable number. Another point to ponder is that there could be places where this actually changes the plan significantly for the better. Pre-filtering one side of a join might, for example, reduce the amount of data on one side to the point where a hash join is chosen over some other strategy. I don't know that this will actually help all that many people but the best case is pretty dramatic for those it does help: the partial qual might be almost as selective as the join condition itself. > (I wonder if it'd be worth inserting a check that > there's not already a manually-generated equivalent clause, too ...) Sounds a little too clever IMHO. > A very nice thing about this is we could do this step ahead of relation > size estimate setting and thus remove the redundant work that currently > happens in set_plain_rel_size when the optimization fires. Which is > another aspect of the current code that's a hack, so getting rid of it > would be a net reduction in hackiness. I'm not sure that would save anything measurable performance-wise, but the hackiness reduction would be nice to have. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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 Templates S03E11
Dimitri Fontaine writes: > Stephen Frost writes: >> That's only accurate if the new mechanism supports .so's, which seems >> unlikely to be the case. > Really? Yes, really. > So now, you don't need anymore to have file system write privileges into > a central place owned by root, it can be anywhere else, Modern OSes have security checks that can prevent loading libraries from random places. This is widely seen as not merely a good thing, but security-critical for network-exposed daemons. Of which we are one. I keep telling you this, and it keeps not sinking in. One more time: any feature that does what you want will be dead on arrival so far as vendors like Red Hat are concerned. I don't care how creatively you argue for it, they will refuse to ship it (or at least refuse to disable the SELinux policy that prevents it). Period. Please stop wasting my time with suggestions otherwise, because it won't happen. So what we have left to discuss is whether we want to develop, and base a community extension-distribution infrastructure on, a mechanism that some popular vendors will actively block. I'm inclined to think it's a bad idea, but I just work here. > If you don't like what I'm building because it's not solving the problem > you want to solve⦠well don't use what I'm building, right? What worries me is that time and effort will go into this instead of something that would be universally acceptable/useful. I grant that there are some installations whose security policies are weak enough that they could use what you want to build. But I'm not sure how many there are, and I'm worried about market fragmentation if we need to have more than one distribution mechanism. Of course, we're already talking about two distribution infrastructures (one for packages including .so's, and one for those without). I see no way around that unless we settle for the status quo. But what you're suggesting will end up with three distribution infrastructures, with duplicative methods for packages including .so's depending on whether they're destined for security-conscious or less-security-conscious platforms. I don't want to end up with that. 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] Minor comment improvement
On Fri, Dec 13, 2013 at 06:53:02PM +0900, Etsuro Fujita wrote: > This is a small patch a bit improving a comment in > src/backend/commands/copy.c. Thanks, applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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: make_timestamp function
There are also timezones off by 15 minutes (although only a few, mainly Nepal). The only integer representation I've ever seen is in 15 minutes units. http://www.timeanddate.com/time/time-zones-interesting.html On Tue, Dec 17, 2013 at 12:33 PM, Bruce Momjian wrote: > On Tue, Dec 17, 2013 at 06:07:38PM +0100, Pavel Stehule wrote: > > Hello > > > > > > 2013/12/17 Pavel Stehule > > > > > > > > > > 2013/12/17 Tom Lane > > > > Alvaro Herrera writes: > > > Yeah, I think a constructor should allow a text timezone. > > > > Yes. I think a numeric timezone parameter is about 99% useless, > > and if you do happen to need that behavior you can just cast the > > numeric to text no? > > > > > > yes, it is possible. Although fully numeric API is much more > consistent. > > > > > > > > I was wrong - there are timezones with minutes like Iran = '1:30'; > > > > so int in hours is bad type - so only text is probably best > > I think India is the big non-integer timezone offset country: > > http://www.timeanddate.com/worldclock/city.html?n=176 > UTC/GMT +5:30 hours > > -- > Bruce Momjian http://momjian.us > EnterpriseDB http://enterprisedb.com > > + Everyone has their own god. + > > > -- > 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] Why no INSTEAD OF triggers on tables?
On 12/16/2013 07:53 PM, Robert Haas wrote: > So, put a BEFORE trigger, and make it return NULL. Same effect, > different notation. NOT the same: Master partition table with BEFORE trigger: josh=# insert into a ( id, val ) values ( 23, 'test' ), ( 24, 'test'), (25,'test'); INSERT 0 0 ^^^ View with INSTEAD OF trigger: josh=# insert into a_v ( id, val ) values ( 23, 'test' ), ( 24, 'test'), (25,'test'); INSERT 0 3 ^^^ The difference here is that the INSTEAD OF trigger returns a rows-affected count, and the BEFORE trigger does not (it returns 0). Some drivers and ORMs, most notably Hibernate, check this rows-returned count, and error if they don't match the rows sent. -- 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] Extension Templates S03E11
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote: > Stephen Frost writes: > >> My thinking is that if we invent a new mechanism for extensions that are > >> not managed like contribs, we will find out that only contribs are going > >> to be using extensions. > > > > That's only accurate if the new mechanism supports .so's, which seems > > unlikely to be the case. > > Really? Yes. The 'new mechanism' to which I was referring was when the entire XXX (extension, library, package, whatever) is in the PG catalog and not managed through files on the filesystem, as contrib-like extensions are. I'm quite aware that what you're asking for is technically possible- that's not what this discussion is about. > The only reason why the current proposal mention *nothing* about how to > deal with modules (.so etc) is because each and every time a mention is > made about that problem, the answer from Tom is “rejected, full stop”. Perhaps I'm not making myself clear here, but *I agree with Tom* on this point. > > There would also be flexability in that an author might choose to use an > > extension even in cases where it's not strictly necessary to do so, for > > whatever reason they want. > > Note that of course you can still install proper OS packages when we > ship with support for Extension Templates. With the various naming conflicts and other risks associated with doing that, which I don't believe were very clearly addressed. An off-the-cuff answer to that issue is not sufficient, imv. > You still didn't propose any other way to have at it, where it's already > my fourth detailed proposal. I didn't outline a proposal which provides what you want, no. That was intentional. > I did spend time on designing what I think > you're trying to say hand-wavely in that exchange, and I don't quite > like the result, as I see now way for it not to entirely deprecate > extensions. I don't think we need to, nor should we, deprecate extensions entirely when that's the approach which *should be* used for .so requiring extensions. Obviously, that's my opinion, and you don't agree with it, and it seems neither of us is willing to shift from that position. > Maybe the proper answer is that we should actually confine extensions to > being the way to install contribs and nothing else, and deprecate them > for cases where you don't have an OS level package. It seems really > strange to build a facility with such a generic name as “extension” only > to resist changing any of it, then stop using it at first opportunity. I'm open to changing how extensions work, to adding dependency information and making other improvements. Being interested in improving the extension system doesn't mean I'm required to support shipping .so's in this manner or installing text script blobs into catalog tables. > > However, as I understand it from the various discussions on this topic > > outside of this list, the immediate concern is the need for a multi-OS > > extension distribution network with support for binaries, .so's and > > .dll's and whatever else, to make installing extensions easier for > > developers on various platforms. I'm all for someone building that and > > dealing with the issues associated with that, but building a client for > > it in core, either in a way where a backend would reach out and > > download the files or accepting binary .so's through the frontend > > protocol, isn't the first step in that and I very much doubt it would > > ever make sense. > > That's exactly the reason why the first piece of that proposal has > absolutely nothing to do with building said client, and is all about how > NOT to have to build it in core *ever*. You can already build what you're after without extension templates entirely, if you're allowing files to be stashed out on the filesystem anywhere. Your argument that you need root doesn't hold any water with me on this issue- there's quite a few mechanisms out there already which allow you to trivially become root. You can write pl/perlu which sudo's and apt-get installs your favorite extension, if you like. That doesn't mean we should build a system into core which tries to do that for you. And, yes, I know that you pushed for and got the GUC in to allow you to have other places to pull .so's from. Having that flexibility doesn't mean we have to support populating that directory from PG. You probably would have been better off pushing for a GUC that allowed a '.d' like directory system for extensions to be defined in. That *still* doesn't require extension templates, storing SQL scripts as text blobs in catalog tables, and you can even avoid the whole 'root' concern if you want. > If you don't like what I'm building because it's not solving the problem > you want to solve… well don't use what I'm building, right? I'm pretty sure that I've pointed out a number of issues that go well beyond not liking it. Thanks, Stephen signature.asc Descripti
Re: [HACKERS] pg_rewarm status
On Tue, Dec 17, 2013 at 8:02 AM, Jim Nasby wrote: > On 12/17/13, 8:34 AM, Robert Haas wrote: > >> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila >> wrote: >> >>> I have used pg_prewarm during some of work related to Buffer Management >>> and >>> other performance related work. It is quite useful utility. >>> +1 for reviving this patch for 9.4 >>> >> >> Any other votes? >> > > We've had to manually code something that runs EXPLAIN ANALYZE SELECT * > from a bunch of tables to warm our caches after a restart, but there's > numerous flaws to that approach obviously. > > Unfortunately, what we really need to warm isn't the PG buffers, it's the > FS cache, which I suspect this won't help. But I still see where just > pg_buffers would be useful for a lot of folks, so +1. Since it doesn't use directIO, you can't warm the PG buffers without also warming FS cache as a side effect. That is why I like 'buffer' as the default--if the data fits in shared_buffers, it warm those, otherwise it at least warms the FS. If you want to only warm the FS cache, you can use either the 'prefetch' or 'read' modes instead. Cheers, Jeff
Re: [HACKERS] patch: make_timestamp function
On Tue, Dec 17, 2013 at 06:07:38PM +0100, Pavel Stehule wrote: > Hello > > > 2013/12/17 Pavel Stehule > > > > > 2013/12/17 Tom Lane > > Alvaro Herrera writes: > > Yeah, I think a constructor should allow a text timezone. > > Yes. I think a numeric timezone parameter is about 99% useless, > and if you do happen to need that behavior you can just cast the > numeric to text no? > > > yes, it is possible. Although fully numeric API is much more consistent. > > > > I was wrong - there are timezones with minutes like Iran = '1:30'; > > so int in hours is bad type - so only text is probably best I think India is the big non-integer timezone offset country: http://www.timeanddate.com/worldclock/city.html?n=176 UTC/GMT +5:30 hours -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] planner missing a trick for foreign tables w/OR conditions
Robert Haas writes: > On Mon, Dec 16, 2013 at 6:59 PM, Tom Lane wrote: >> The hard part is not extracting the partial qual. The hard part is >> trying to make sure that adding this entirely-redundant scan qual doesn't >> catastrophically degrade join size estimates. > OK, I had a feeling that's where the problem was likely to be. Do you > have any thoughts about a more principled way of solving this problem? > I mean, off-hand, it's not clear to me that the comments about this > being a MAJOR HACK aren't overstated. Well, the business about injecting the correction by adjusting a cached selectivity is certainly a hack, but it's not one that I think is urgent to get rid of; I don't foresee anything that's likely to break it soon. > I might be missing something, but I suspect it works fine if every > path for the relation is generating the same rows. I had been thinking it would fall down if there are several OR conditions affecting different collections of rels, but after going through the math again, I'm now thinking I was wrong and it does in fact work out. As you say, we do depend on all paths generating the same rows, but since the extracted single-rel quals are inserted as plain baserestrictinfo quals, that'll be true. A bigger potential objection is that we're opening ourselves to larger problems with estimation failures due to correlated qual conditions, but again I'm finding that the math doesn't bear that out. It's reasonable to assume that our estimate for the extracted qual will be better than our estimate for the OR as a whole, so our adjusted size estimates for the filtered base relations are probably OK. And the adjustment to the OR clause selectivity means that the size estimate for the join comes out exactly the same. We'll actually be better off than with what is likely to happen now, which is that people manually extract the simplified condition and insert it into the query explicitly. PG doesn't realize that that's redundant and so will underestimate the join size. So at this point I'm pretty much talked into it. We could eliminate the dependence on indexes entirely, and replace this code with a step that simply tries to pull single-base-relation quals out of ORs wherever it can find one. You could argue that the produced quals would sometimes not be worth testing for, but we could apply a heuristic that says to forget it unless the estimated selectivity of the extracted qual is less than, I dunno, 0.5 maybe. (I wonder if it'd be worth inserting a check that there's not already a manually-generated equivalent clause, too ...) A very nice thing about this is we could do this step ahead of relation size estimate setting and thus remove the redundant work that currently happens in set_plain_rel_size when the optimization fires. Which is another aspect of the current code that's a hack, so getting rid of it would be a net reduction in hackiness. 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 Templates S03E11
Stephen Frost writes: >> My thinking is that if we invent a new mechanism for extensions that are >> not managed like contribs, we will find out that only contribs are going >> to be using extensions. > > That's only accurate if the new mechanism supports .so's, which seems > unlikely to be the case. Really? Look at dynamic_library_path, then at a classic CREATE FUNCTION command that maps into a C provided symbol: CREATE OR REPLACE FUNCTION prefix_range_in(cstring) RETURNS prefix_range AS '$libdir/prefix' LANGUAGE C IMMUTABLE STRICT; A packaging or distribution software will have no problem removing the '$libdir/' part of the magic AS string here. Once removed, prefix.so will be loaded from anywhere on the file system paths listed into the dynamic_library_path GUC. So now, you don't need anymore to have file system write privileges into a central place owned by root, it can be anywhere else, and the backend hooks, when properly setup, will be able to benefit from that. The missing bits are where to find the extension control files and scripts. The only reason why the current proposal mention *nothing* about how to deal with modules (.so etc) is because each and every time a mention is made about that problem, the answer from Tom is “rejected, full stop”. > What I think we'd end up with is a split > between extensions, which would be things containing .so's, and > "libraries" or what-have-you, which would be more-or-less everything > else. That kind of a break-down strikes me as perfectly reasonable. Only if it's the best we can do. > There would also be flexability in that an author might choose to use an > extension even in cases where it's not strictly necessary to do so, for > whatever reason they want. Note that of course you can still install proper OS packages when we ship with support for Extension Templates. > I'd like to see extensions improved. I don't feel like the proposed > 'extension templates' is the way to do that because I don't think it > really solves anything and it adds a layer that strikes me as wholly > unnecessary. You still didn't propose any other way to have at it, where it's already my fourth detailed proposal. I did spend time on designing what I think you're trying to say hand-wavely in that exchange, and I don't quite like the result, as I see now way for it not to entirely deprecate extensions. Maybe the proper answer is that we should actually confine extensions to being the way to install contribs and nothing else, and deprecate them for cases where you don't have an OS level package. It seems really strange to build a facility with such a generic name as “extension” only to resist changing any of it, then stop using it at first opportunity. Also, I'm not sure about the consequences in terms of user trust if we build something new to solve a use case that looks so much the same. > However, as I understand it from the various discussions on this topic > outside of this list, the immediate concern is the need for a multi-OS > extension distribution network with support for binaries, .so's and > .dll's and whatever else, to make installing extensions easier for > developers on various platforms. I'm all for someone building that and > dealing with the issues associated with that, but building a client for > it in core, either in a way where a backend would reach out and > download the files or accepting binary .so's through the frontend > protocol, isn't the first step in that and I very much doubt it would > ever make sense. That's exactly the reason why the first piece of that proposal has absolutely nothing to do with building said client, and is all about how NOT to have to build it in core *ever*. If you don't like what I'm building because it's not solving the problem you want to solve… well don't use what I'm building, right? 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] Patch: show xid and xmin in pg_stat_activity and pg_stat_replication
On Tue, Dec 17, 2013 at 9:58 AM, Christian Kruse wrote: > Hi, > > attached you will find a patch for showing the current transaction id > (xid) and the xmin of a backend in pg_stat_activty and the xmin in > pg_stat_replication. > > This may be helpful when looking for the cause of bloat. > > I added two new struct members in PgBackendStatus which get filled in > pgstat_read_current_status() and slightly modified the catalog schema > and the pg_stat_get_activity() procedure. > > I'm not sure if it is a good idea to gather the data in > pgstat_read_current_status(), but I chose to do it this way > nonetheless because otherwise I would have to create collector > functions like pgstat_report_xid_assignment() / > pgstat_report_xmin_changed() (accordingly to > pgstat_report_xact_timestamp()) which may result in a performance hit. Please add your patch here so we don't lose track of it: https://commitfest.postgresql.org/action/commitfest_view/open -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: make_timestamp function
Hello 2013/12/17 Pavel Stehule > > > > 2013/12/17 Tom Lane > >> Alvaro Herrera writes: >> > Yeah, I think a constructor should allow a text timezone. >> >> Yes. I think a numeric timezone parameter is about 99% useless, >> and if you do happen to need that behavior you can just cast the >> numeric to text no? > > > yes, it is possible. Although fully numeric API is much more consistent. > > I was wrong - there are timezones with minutes like Iran = '1:30'; so int in hours is bad type - so only text is probably best Pavel > Pavel > > >> >> regards, tom lane >> > >
Re: [HACKERS] row security roadmap proposal
On Mon, Dec 16, 2013 at 3:12 PM, Gregory Smith wrote: > On 12/16/13 9:36 AM, Craig Ringer wrote: >> >> - Finish and commit updatable security barrier views. I've still got a >> lot of straightening out to do there. > > I don't follow why you've put this part first. It has a lot of new > development and the risks that go along with that, but the POC projects I've > been testing are more interested in the view side issues. I don't really see a way that any of this can work without that. To be clear, that work is required even just for read-side security. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: make_timestamp function
2013/12/17 Alvaro Herrera > Pavel Stehule escribió: > > > but AT TIME ZONE allows double (but decimal parts is ignored quietly) > > > > postgres=# select make_time(10,20,30) at time zone '+10.2'; > > timezone > > - > > 23:20:30-10 > > > > so I propose (and I implemented) a variant with int as time zone > > > > and we can (if we would) implement next one with text as time zone > > Yeah, I think a constructor should allow a text timezone. > is there some simple way, how to parse text time zone? Regards Pavel > > -- > Álvaro Herrerahttp://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services >
Re: [HACKERS] ANALYZE sampling is too good
On 12/17/2013 12:06 AM, Jeff Janes wrote: On Mon, Dec 9, 2013 at 3:14 PM, Heikki Linnakangas wrote: I took a stab at using posix_fadvise() in ANALYZE. It turned out to be very easy, patch attached. Your mileage may vary, but I'm seeing a nice gain from this on my laptop. Taking a 3 page sample of a table with 717717 pages (ie. slightly larger than RAM), ANALYZE takes about 6 seconds without the patch, and less than a second with the patch, with effective_io_concurrency=10. If anyone with a good test data set loaded would like to test this and post some numbers, that would be great. Performance is often chaotic near transition points, so I try to avoid data sets that are slightly bigger or slightly smaller than RAM (or some other limit). Do you know how many io channels your SSD has (or whatever the term of art is for SSD drives)? No idea. It's an Intel 335. On a RAID with 12 spindles, analyzing pgbench_accounts at scale 1000 (13GB) with 4 GB of RAM goes from ~106 seconds to ~19 seconds. However, I'm not sure what problem we want to solve here. The case that Greg Stark mentioned in the email starting this thread is doing a database-wide ANALYZE after an upgrade. In that use case, you certainly want to get it done as quickly as possible, using all the available resources. I certainly would not wish to give a background maintenance process permission to confiscate my entire RAID throughput for its own operation. Then don't set effective_io_concurrency. If you're worried about that, you probably wouldn't want any other process to monopolize the RAID array either. Perhaps this could only be active for explicit analyze, and only if vacuum_cost_delay=0? That would be a bit weird, because ANALYZE in general doesn't obey vacuum_cost_delay. Maybe it should, though... Perhaps there should be something like "alter background role autovac set ...". Otherwise we are going to end up with an "autovacuum_*" shadow parameter for many of our parameters, see "autovacuum_work_mem" discussions. Yeah, so it seems. - Heikki -- 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: make_timestamp function
2013/12/17 Tom Lane > Alvaro Herrera writes: > > Yeah, I think a constructor should allow a text timezone. > > Yes. I think a numeric timezone parameter is about 99% useless, > and if you do happen to need that behavior you can just cast the > numeric to text no? yes, it is possible. Although fully numeric API is much more consistent. Pavel > > regards, tom lane >
Re: [HACKERS] patch: make_timestamp function
Alvaro Herrera writes: > Yeah, I think a constructor should allow a text timezone. Yes. I think a numeric timezone parameter is about 99% useless, and if you do happen to need that behavior you can just cast the numeric to text no? 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] pg_rewarm status
On Tue, Dec 17, 2013 at 11:02 AM, Jim Nasby wrote: > On 12/17/13, 8:34 AM, Robert Haas wrote: >> >> On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila >> wrote: >>> >>> I have used pg_prewarm during some of work related to Buffer Management >>> and >>> other performance related work. It is quite useful utility. >>> +1 for reviving this patch for 9.4 >> >> >> Any other votes? > > > We've had to manually code something that runs EXPLAIN ANALYZE SELECT * from > a bunch of tables to warm our caches after a restart, but there's numerous > flaws to that approach obviously. > > Unfortunately, what we really need to warm isn't the PG buffers, it's the FS > cache, which I suspect this won't help. But I still see where just > pg_buffers would be useful for a lot of folks, so +1. It'll do either one. For the FS cache, on Linux, you can also use pgfincore. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SSL: better default ciphersuite
On Tue, Dec 17, 2013 at 09:51:30AM -0500, Robert Haas wrote: > On Sun, Dec 15, 2013 at 5:10 PM, James Cloos wrote: > > For reference, see: > > > > https://wiki.mozilla.org/Security/Server_Side_TLS > > > > for the currently suggested suite for TLS servers. > ... > > But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful > > for some. And RC4, perhaps, also should be !ed. > > > > And if anyone wants Kerberos tls-authentication, one could add > > KRB5-DES-CBC3-SHA, but that is ssl3-only. > > > > Once salsa20-poly1305 lands in openssl, that should be added to the > > start of the list. > > I'm starting to think we should just leave this well enough alone. We > can't seem to find two people with the same idea of what would be > better than what we have now. And of course the point of making it a > setting in the first place is that each person can set it to whatever > they deem best. Yes, I am seeing that too. Can we agree on one that is _better_ than what we have now, even if we can't agree on a _best_ one? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] pg_rewarm status
Robert Haas wrote: > Amit Kapila wrote: >> I have used pg_prewarm during some of work related to Buffer >> Management and other performance related work. It is quite >> useful utility. >> +1 for reviving this patch for 9.4 > > Any other votes? Where I would have used a prewarm utility is following an off-hours VACUUM FREEZE run. Where this maintenance made sense the only downside I saw was a brief period in the mornings where the cache was not populated with the "hot" data, and performance was somewhat degraded until the cache settled in again. So, +1. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: make_timestamp function
Pavel Stehule escribió: > but AT TIME ZONE allows double (but decimal parts is ignored quietly) > > postgres=# select make_time(10,20,30) at time zone '+10.2'; > timezone > - > 23:20:30-10 > > so I propose (and I implemented) a variant with int as time zone > > and we can (if we would) implement next one with text as time zone Yeah, I think a constructor should allow a text timezone. -- Álvaro Herrerahttp://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] patch: make_timestamp function
2013/12/17 Jim Nasby > On 12/15/13, 12:59 PM, Pavel Stehule wrote: > >> Why wouldn't we have a version that optionally accepts the timezone? >> That mirrors what you can currently do with a cast from text, and having to >> set the GUC if you need a different TZ would be a real PITA. >> >> >> It is not bad idea. >> >> What will be format for timezone in this case? Is a doble enough? >> > > Sorry for not seeing this earlier, but no, I think double is barking up > the wrong tree. It should accept the same timezone identifiers that the > rest of the system does, like blah AT TIME ZONE foo and SET timezone = foo; > I checked a code from datetime parser, and there we are not consistent postgres=# select '1973-07-15 08:15:55.33+02'::timestamptz; timestamptz --- 1973-07-15 07:15:55.33+01 (1 row) postgres=# select '1973-07-15 08:15:55.33+02.2'::timestamptz; ERROR: invalid input syntax for type timestamp with time zone: "1973-07-15 08:15:55.33+02.2" LINE 1: select '1973-07-15 08:15:55.33+02.2'::timestamptz; It allows only integer but AT TIME ZONE allows double (but decimal parts is ignored quietly) postgres=# select make_time(10,20,30) at time zone '+10.2'; timezone - 23:20:30-10 so I propose (and I implemented) a variant with int as time zone and we can (if we would) implement next one with text as time zone Regards Pavel > > Specifically, it needs to support things like 'GMT' and 'CST6CDT'. > > I can see an argument for another version that accepts numeric so if you > want to do -11.5 you don't have to wrap it in quotes... > > -- > Jim C. Nasby, Data Architect j...@nasby.net > 512.569.9461 (cell) http://jim.nasby.net >
Re: [HACKERS] 9.3 reference constraint regression
Andres Freund wrote:
> On 2013-12-16 17:43:37 -0300, Alvaro Herrera wrote:
> > Alvaro Herrera wrote:
> >
> > > This POC patch changes the two places in HeapTupleSatisfiesUpdate that
> > > need to be touched for this to work. This is probably too simplistic,
> > > in that I make the involved cases return HeapTupleBeingUpdated without
> > > checking that there actually are remote lockers, which is the case of
> > > concern. I'm not yet sure if this is the final form of the fix, or
> > > instead we should expand the Multi (in the cases where there is a multi)
> > > and verify whether any lockers are transactions other than the current
> > > one. As is, nothing seems to break, but I think that's probably just
> > > chance and should not be relied upon.
> >
> > After playing with this, I think the reason this seems to work without
> > fail is that all callers of HeapTupleSatisfiesUpdate are already
> > prepared to deal with the case where HeapTupleBeingUpdated is returned
> > but there is no actual transaction that would block the operation.
> > So I think the proposed patch is okay, barring a few more comments.
>
> Are you sure? the various wait/nowait cases don't seem to handle that
> correctly.
Well, it would help if those cases weren't dead code. Neither
heap_update nor heap_delete are ever called in the "no wait" case at
all. Only heap_lock_tuple is, and I can't see any misbehavior there
either, even with HeapTupleBeingUpdated returned when there's a
non-local locker, or when there's a MultiXact as xmax, regardless of its
status.
Don't get me wrong --- it's not like this case is all that difficult to
handle. All that's required is something like this in
HeapTupleSatisfiesUpdate:
if (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetXmin(tuple)))
{
...
if (HEAP_XMAX_IS_LOCKED_ONLY(tuple->t_infomask))/* not deleter
*/
{
if (tuple->t_infomask & HEAP_XMAX_IS_MULTI)
{
intnmembers;
boolremote;
inti;
MultiXactMember *members;
nmembers =
GetMultiXactIdMembers(HeapTupleHeaderGetRawXmax(tuple),
&members, false);
remote = false;
for (i = 0; i < nmembers; i++)
{
if
(!TransactionIdIsCurrentTransactionId(members[i].xid))
{
remote = true;
break;
}
}
if (nmembers > 0)
pfree(members);
if (remote)
return HeapTupleBeingUpdated;
else
return HeapTupleMayBeUpdated;
}
else if
(!TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmax(tuple)))
return HeapTupleBeingUpdated;
return HeapTupleMayBeUpdated;
}
}
The simpler code just does away with the GetMultiXactIdMembers() and
returns HeapTupleBeingUpdated always. In absence of a test case that
misbehaves with that, it's hard to see that it is a good idea to go all
this effort there.
--
Álvaro Herrerahttp://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] pg_rewarm status
On 12/17/13, 8:34 AM, Robert Haas wrote: On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila wrote: I have used pg_prewarm during some of work related to Buffer Management and other performance related work. It is quite useful utility. +1 for reviving this patch for 9.4 Any other votes? We've had to manually code something that runs EXPLAIN ANALYZE SELECT * from a bunch of tables to warm our caches after a restart, but there's numerous flaws to that approach obviously. Unfortunately, what we really need to warm isn't the PG buffers, it's the FS cache, which I suspect this won't help. But I still see where just pg_buffers would be useful for a lot of folks, so +1. -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.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] patch: make_timestamp function
On 12/15/13, 12:59 PM, Pavel Stehule wrote: Why wouldn't we have a version that optionally accepts the timezone? That mirrors what you can currently do with a cast from text, and having to set the GUC if you need a different TZ would be a real PITA. It is not bad idea. What will be format for timezone in this case? Is a doble enough? Sorry for not seeing this earlier, but no, I think double is barking up the wrong tree. It should accept the same timezone identifiers that the rest of the system does, like blah AT TIME ZONE foo and SET timezone = foo; Specifically, it needs to support things like 'GMT' and 'CST6CDT'. I can see an argument for another version that accepts numeric so if you want to do -11.5 you don't have to wrap it in quotes... -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.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] commit fest 2013-11 final report
Peter Eisentraut writes: > Committers should now try to get the Ready for Committer queue cleared. I'm still hacking on the WITHIN GROUP patch, and will look at the planner indexes thing after that's done. > That leaves 8 patches that haven't been reviewed. If you take a look, > you can imagine why: They're the most complicated and obscure topics, > combined with reviewers who signed up but didn't get to do a review, > plus perhaps authors who were too busy to follow up aggressively. I'll > leave those open in case someone still wants to take a look. Authors > should move those patches forward to the next commit fest if they want. Perhaps we should just move all the Needs Review and RFC patches forward to the next fest, so we don't forget about them? 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: make_timestamp function
Hello
updated patch
time zone type functions are overloaded now
postgres=# select '1973-07-15 08:15:55.33+02'::timestamptz;
timestamptz
---
1973-07-15 07:15:55.33+01
(1 row)
postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33, 2);
make_timestamptz
---
1973-07-15 07:15:55.33+01
(1 row)
postgres=# SELECT make_timestamptz(1973, 07, 15, 08, 15, 55.33);
make_timestamptz
---
1973-07-15 08:15:55.33+01
(1 row)
Regards
Pavel
2013/12/15 Pavel Stehule
> Hello
>
>
> 2013/12/13 Jim Nasby
>
>> On 12/13/13 1:49 PM, Fabrízio de Royes Mello wrote:
>>
>>>
>>> On Fri, Dec 13, 2013 at 5:35 PM, Tom Lane >> t...@sss.pgh.pa.us>> wrote:
>>>
>>> >
>>> > =?ISO-8859-1?Q?Fabr=EDzio_de_Royes_Mello?=
>>> >> fabriziome...@gmail.com>> writes:
>>> > > I think the goal of the "make_date/time/timestamp" function series
>>> is build
>>> > > a date/time/timestamp from scratch, so the use of
>>> 'make_timestamptz' is to
>>> > > build a specific timestamp with timezone and don't convert it.
>>> >
>>> > Yeah; we don't really want to incur an extra timezone rotation just
>>> to get
>>> > to a timestamptz. However, it's not clear to me if make_timestamptz()
>>> > needs to have an explicit zone parameter or not. It could just assume
>>> > that you meant the active timezone.
>>> >
>>>
>>> +1. And if you want a different timezone you can just set the 'timezone'
>>> GUC.
>>>
>>
>> Why wouldn't we have a version that optionally accepts the timezone? That
>> mirrors what you can currently do with a cast from text, and having to set
>> the GUC if you need a different TZ would be a real PITA.
>>
>
> It is not bad idea.
>
> What will be format for timezone in this case? Is a doble enough?
>
> last version of this patch attached (without overloading in this moment)
>
>
>
>
>> --
>> Jim C. Nasby, Data Architect j...@nasby.net
>> 512.569.9461 (cell) http://jim.nasby.net
>>
>
>
commit 7f03cda7a5a5e173b51b2ddc87c1e437a5dc7b34
Author: Pavel Stehule
Date: Thu Dec 12 18:08:47 2013 +0100
initial implementation make_timestamp
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a411e3a..9adec6b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -6735,6 +6735,78 @@ SELECT SUBSTRING('XY1234Z', 'Y*?([0-9]{1,3})');
+ make_timetz
+
+
+
+ make_timetz(hour int,
+ min int,
+ sec double precision,
+timezone int )
+
+
+
+time with time zone
+
+ Create time with time zone from hour, minute and seconds fields
+
+make_timetz(8, 15, 23.5)
+08:15:23.5+01
+
+
+
+
+
+ make_timestamp
+
+
+
+ make_timestamp(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision)
+
+
+
+timestamp
+
+ Create timestamp from year, month, day, hour, minute and seconds fields
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5
+
+
+
+
+
+ make_timestamptz
+
+
+
+ make_timestamptz(year int,
+ month int,
+ day int,
+ hour int,
+ min int,
+ sec double precision,
+timezone int )
+
+
+
+timestamp with time zone
+
+ Create timestamp with time zone from year, month, day, hour, minute
+ and seconds fields
+
+make_timestamp(1-23, 7, 15, 8, 15, 23.5)
+2013-07-15 08:15:23.5+01
+
+
+
+
+
now
now()
diff --git a/src/backend/utils/adt/date.c b/src/backend/utils/adt/date.c
index fe091da..08831d6 100644
--- a/src/backend/utils/adt/date.c
+++ b/src/backend/utils/adt/date.c
@@ -1246,38 +1246,95 @@ timetypmodout(PG_FUNCTION_ARGS)
}
/*
- * make_time - time constructor
+ * time constructor used for make_time and make_timetz
*/
-Datum
-make_time(PG_FUNCTION_ARGS)
+static TimeADT
+make_time_internal(int hour, int min, double sec)
{
- int tm_hour = PG_GETARG_INT32(0);
- int tm_min = PG_GETARG_INT32(1);
- double sec = PG_GETARG_FLOAT8(2);
TimeADT time;
/* This should match the checks in DecodeTimeOnly */
- if (tm_hour < 0 || tm_min < 0 || tm_min > MINS_PER_HOUR - 1 ||
+ if (hour < 0 || min < 0 || min > MINS_PER_HOUR - 1 ||
sec < 0 || sec > SECS_PER_MINUTE ||
- tm_hour > HOURS_PER_DAY ||
+ hour > HOURS_PER_DAY ||
/* test for > 24:00:00 */
- (tm_hour == HOURS_PER_DAY && (tm_min > 0 || sec > 0)))
+ (hour == HOURS_PER_DAY && (min > 0 || sec > 0)))
ereport(ERROR,
(errcode(ERRCODE_DATETI
Re: [HACKERS] Extension Templates S03E11
* Dimitri Fontaine (dimi...@2ndquadrant.fr) wrote: > > OTOH, for a set of pure-SQL objects, it's not necessary that there be a > > canonical text file somewhere, and we have in principle complete knowledge > > of the objects' semantics as well as the ability to dump-and-restore into > > newer PG versions. So it's not at all clear that we should just adopt the > > existing model with the smallest possible changes --- which AFAICS is > > basically what this proposal is. Maybe that's the way to go, but we > > should consider alternatives, and in particular I think there is much > > more reason to allow inside-the-database mutation of the SQL objects. > > My thinking is that if we invent a new mechanism for extensions that are > not managed like contribs, we will find out that only contribs are going > to be using extensions. That's only accurate if the new mechanism supports .so's, which seems unlikely to be the case. What I think we'd end up with is a split between extensions, which would be things containing .so's, and "libraries" or what-have-you, which would be more-or-less everything else. That kind of a break-down strikes me as perfectly reasonable. There would also be flexability in that an author might choose to use an extension even in cases where it's not strictly necessary to do so, for whatever reason they want. > Given the options of either growing extensions into being able to cope > with more than a single model or building an entirely new system having > most of the same feature set than Extensions, I'm pushing for the option > where we build on top of what we have already. I'm not sure that we need to throw away everything that exists to add on this new capability; perhaps we can build a generic versioned object-container system on which extensions and packages/libraries/classes/whatever can also be built on (or perhaps that's what 'extensions' end up morphing into). > We can't use “package” because it means something very different in > direct competition. I have other propositions, but they are only > relevant if we choose not to improve Extensions… right? I'd like to see extensions improved. I don't feel like the proposed 'extension templates' is the way to do that because I don't think it really solves anything and it adds a layer that strikes me as wholly unnecessary. I could see pulling in the control file contents as a catalog, adding in dependency information which could be checked against, perhaps hard vs. soft dependencies, and other things that make sense to track for extensions-currently-installed into a given database. However, as I understand it from the various discussions on this topic outside of this list, the immediate concern is the need for a multi-OS extension distribution network with support for binaries, .so's and .dll's and whatever else, to make installing extensions easier for developers on various platforms. I'm all for someone building that and dealing with the issues associated with that, but building a client for it in core, either in a way where a backend would reach out and download the files or accepting binary .so's through the frontend protocol, isn't the first step in that and I very much doubt it would ever make sense. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] "stuck spinlock"
On Mon, Dec 16, 2013 at 6:46 AM, Tom Lane wrote: > Andres Freund writes: > > Hard to say, the issues fixed in the release are quite important as > > well. I'd tend to say they are more important. I think we just need to > > release 9.3.3 pretty soon. > > Yeah. > Has there been any talk about when a 9.3.3 (and/or 9.2.7?) patch might be released?
Re: [HACKERS] Proposal: variant of regclass
On Mon, Dec 16, 2013 at 8:22 PM, Tatsuo Ishii wrote: >>> static Datum regclass_gut(char *class_name_or_oid, bool raiseError); >>> static List *stringToQualifiedNameList_gut(const char *string, bool >>> raiseError); >> >> Please spell that as "guts" not "gut". > > Thanks. I see. > >>> regclass_gut is called from regclassin and toregclass and do the most >>> job before regclassin did. "raiseError" flag controls whether an error >>> is raised or not when an invalid argument (for example non existent >>> relation) is given. For this purpose, regclass_gut wraps the call to >>> oidin using a PG_TRY block. >> >> I do not think that use of PG_TRY is either necessary or acceptable >> --- for example, it will happily trap and discard query-cancel errors, >> as well as other errors that are not necessarily safe to ignore. >> If you don't want to risk an error on an invalid numeric string, >> how about parsing the integer yourself with sscanf? > > Fair enough. I will remove the part. > >> More generally, though, I don't see a great need to try to promise >> that this function *never* throws any errors, and so I'm also suspicious >> of the hacking you've done on stringToQualifiedNameList. I'm even >> less happy about the idea that this patch might start reaching into >> things like makeRangeVarFromNameList. I think it's sufficient if it >> doesn't throw an error on name-not-found; you don't have to try to >> prevent weird syntax problems from throwing errors. > > For the pgpool-II use case, I'm happy to follow you because pgpool-II > always does a grammatical check (using raw parser) on a query first > and such syntax problem will be pointed out, thus never reaches to > the state where calling toregclass. > > One concern is, other users expect toregclass to detect such syntax > problems. Anybody? It seems fine to me if the new function ignores the specific error of "relation does not exist" while continuing to throw other errors. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] SSL: better default ciphersuite
On Sun, Dec 15, 2013 at 5:10 PM, James Cloos wrote: > For reference, see: > > https://wiki.mozilla.org/Security/Server_Side_TLS > > for the currently suggested suite for TLS servers. ... > But for pgsql, I'd leave off the !PSK; pre-shared keys may prove useful > for some. And RC4, perhaps, also should be !ed. > > And if anyone wants Kerberos tls-authentication, one could add > KRB5-DES-CBC3-SHA, but that is ssl3-only. > > Once salsa20-poly1305 lands in openssl, that should be added to the > start of the list. I'm starting to think we should just leave this well enough alone. We can't seem to find two people with the same idea of what would be better than what we have now. And of course the point of making it a setting in the first place is that each person can set it to whatever they deem best. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Changeset Extraction Interfaces
On Tue, Dec 17, 2013 at 4:31 AM, Andres Freund wrote: > On 2013-12-16 23:01:16 -0500, Robert Haas wrote: >> On Sat, Dec 14, 2013 at 12:37 PM, Andres Freund >> wrote: >> > On 2013-12-14 11:50:00 -0500, Robert Haas wrote: >> >> Well, it still seems to me that the right way to think about this is >> >> that the change stream begins at a certain point, and then once you >> >> cross a certain threshold (all transactions in progress at that time >> >> have ended) any subsequent snapshot is a possible point from which to >> >> roll forward. >> > >> > Unfortunately it's not possible to build exportable snapshots at any >> > time - it requires keeping far more state around since we need to care >> > about all transactions, not just transactions touching the >> > catalog. Currently you can only export the snapshot in the one point we >> > become consistent, after that we stop maintaining that state. >> >> I don't get it. Once all the old transactions are gone, I don't see >> why you need any state at all to build an exportable snapshot. Just >> take a snapshot. > > The state we're currently decoding, somewhere in already fsynced WAL, > won't correspond to the state in the procarray. There might be > situations where it will, but we can't guarantee that we ever reach that > point without taking locks that will be problematic. You don't need to guarantee that. Just take a current snapshot and then throw away (or don't decode in the first place) any transactions that would be visible to that snapshot. This is simpler and more flexible, and possibly more performant, too, because with your design you'll have to hold back xmin to the historical snapshot you build while copying the table rather than to a current snapshot. I really think we should consider whether we can't get by with ripping out the build-an-exportable-snapshot code altogether. I don't see that it's really buying us much. We need a way for the client to know when decoding has reached the point where it is guaranteed complete - i.e. all transactions in progress at the time decoding was initiated have ended. We also need a way for a backend performing decoding to take a current MVCC snapshot, export it, and send the identifier to the client. And we need a way for the client to know whether any given one of those snapshots includes a particular XID we may have decoded. But I think all of that might still be simpler than what you have now, and it's definitely more flexible. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Useless "Replica Identity: NOTHING" noise from psql \d
On Mon, Dec 16, 2013 at 7:25 AM, Andres Freund wrote: > On 2013-12-14 17:43:36 +0100, Andres Freund wrote: >> On 2013-12-14 11:27:53 -0500, Tom Lane wrote: >> > In HEAD: >> > >> > regression=# \d pg_depend >> >Table "pg_catalog.pg_depend" >> >Column| Type | Modifiers >> > -+-+--- >> > classid | oid | not null >> > objid | oid | not null >> > objsubid| integer | not null >> > refclassid | oid | not null >> > refobjid| oid | not null >> > refobjsubid | integer | not null >> > deptype | "char" | not null >> > Indexes: >> > "pg_depend_depender_index" btree (classid, objid, objsubid) >> > "pg_depend_reference_index" btree (refclassid, refobjid, refobjsubid) >> > Replica Identity: NOTHING >> > >> > Where did that last line come from, and who thinks it's so important >> > that it should appear by default? It seems absolutely content-free >> > even if I were using whatever feature it refers to, since it is >> > (I presume) the default state. >> >> Hm. Yes, that's slightly inellegant. It's shown because it's not >> actually the normal default normal tables. Just for system tables. Maybe >> we should just set it to default (in pg_class) for system tables as >> well, and just change it in the relcache. > > Hm. I don't like that choice much after thinking for a bit. Seems to > make querying the catalog unneccessarily complex. > How about making it conditional on the table's namespace instead? That > will do the wrong thing if somebody moves a table to pg_catalog and > configures a replica identity, but I think we can live with that, given > how many other things work strangely around that. > > Patch attached. I vote for showing it only with +, but regardless of whether the value matches the expected default. I'd keep the relkind test, though, because I think I noticed that it currently shows up for indexes, which is dumb. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] pg_rewarm status
On Tue, Dec 17, 2013 at 12:09 AM, Amit Kapila wrote: > I have used pg_prewarm during some of work related to Buffer Management and > other performance related work. It is quite useful utility. > +1 for reviving this patch for 9.4 Any other votes? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Why no INSTEAD OF triggers on tables?
On Tue, Dec 17, 2013 at 3:27 AM, Marko Tiikkaja wrote: > On 12/17/13, 4:53 AM, Robert Haas wrote: >>> >>> Well, I'm specifically thinking of master partition tables. In that >>> case, we really want an INSTEAD OF trigger. >> >> /me scratches head. >> >> So, put a BEFORE trigger, and make it return NULL. Same effect, >> different notation. > > > But it's not the same effect at all, that's the point: > > =# create view foov as select 1 as a; > CREATE VIEW > > =# create function insteadof() returns trigger as $$ > $# begin > $# -- INSERT here > $# return new; > $# end > $# $$ language plpgsql; > CREATE FUNCTION > > =# create function before() returns trigger as $$ > $# begin > $# -- INSERT here > $# return null; > $# end > $# $$ language plpgsql; > CREATE FUNCTION > > =# create trigger t1 instead of insert on foov for each row execute > procedure insteadof(); > CREATE TRIGGER > > =# create trigger t2 before insert on bart for each row execute procedure > before(); > CREATE TRIGGER > > =# insert into foov values (1) returning *; > a > --- > 1 > (1 row) > > INSERT 0 1 > > local:marko=#* insert into bart values (1) returning *; > a > --- > (0 rows) > > INSERT 0 0 Ah, interesting point. I didn't realize it worked like that. That does seem like a mighty useful thing to be able to do. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] logical changeset generation v6.8
On Tue, Dec 17, 2013 at 7:48 AM, Andres Freund wrote: > On 2013-12-16 00:53:10 -0500, Robert Haas wrote: >> > Yes, I think we could mostly reuse it, we'd probably want to add a field >> > or two more (application_name, sync_prio?). I have been wondering >> > whether some of the code in replication/logical/logical.c shouldn't be >> > in replication/slot.c or similar. So far I've opted for leaving it in >> > its current place since it would have to change a bit for a more general >> > role. >> >> I strongly favor moving the slot-related code to someplace with "slot" >> in the name, and replication/slot.c seems about right. Even if we >> don't extend them to cover non-logical replication in this release, >> we'll probably do it eventually, and it'd be better if that didn't >> require moving large amounts of code between files. > > Any opinion on the storage location of the slot files? It's currently > pg_llog/$slotname/state[.tmp]. It's a directory so we have a location > during logical decoding to spill data to... pg_replslot? pg_replication_slot? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] logical changeset generation v6.8
On 2013-12-16 00:53:10 -0500, Robert Haas wrote: > > Yes, I think we could mostly reuse it, we'd probably want to add a field > > or two more (application_name, sync_prio?). I have been wondering > > whether some of the code in replication/logical/logical.c shouldn't be > > in replication/slot.c or similar. So far I've opted for leaving it in > > its current place since it would have to change a bit for a more general > > role. > > I strongly favor moving the slot-related code to someplace with "slot" > in the name, and replication/slot.c seems about right. Even if we > don't extend them to cover non-logical replication in this release, > we'll probably do it eventually, and it'd be better if that didn't > require moving large amounts of code between files. Any opinion on the storage location of the slot files? It's currently pg_llog/$slotname/state[.tmp]. It's a directory so we have a location during logical decoding to spill data to... Greetings, Andres Freund -- Andres Freund 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] Optimize kernel readahead using buffer access strategy
On 17 December 2013 11:50, KONDO Mitsumasa wrote: > Unpatched PG is faster than patched in A and B query. It was about 1.3 times > faster. Result of A query as expected, because patched PG cannot execute > readahead at all. So cache cold situation is bad for patched PG. However, it > might good for cache hot situation, because it doesn't read disk IO at all > and can calculate file cache usage and know which cache is important. > > However, result of B query as unexpected, because my patch select > POSIX_FADV_SEQUNENTIAL collectry, but it slow. I cannot understand that, > nevertheless I read kernel source code... Next, I change > POSIX_FADV_SEQUNENTIAL to POISX_FADV_NORMAL in my patch. B query was faster > as unpatched PG. > > In heavily random access benchmark tests which are pgbench and DBT-2, my > patched PG is about 1.1 - 1.3 times faster than unpatched PG. But postgres > buffer hint strategy algorithm have not optimized for readahead strategy > yet, and I don't fix it. It is still only for ring buffer algorithm in > shared_buffer. These are interesting results. Good research. They also show that the benefit of this is very specific to the exact task being performed. I can't see any future for a setting that applies to everything or nothing. We must be more selective. We also need much better benchmark results, clearly laid out, so they can be reproduced and discussed. Please keep working on this. -- 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
[HACKERS] [bug fix] connection service file doesn't take effect with ECPG apps
Hello, I've found a small bug of ECPG and attached a patch. I tested the fix with 9.4. I'd like the fix to be back-ported. [Problem] The ECPG app runs the statement: EXEC SQL CONNECT TO 'tcp:postgresql://?service=my_service'; I want this app to connect to any database based on the connection service file. For example, I wrote the following connection service file pg_service.conf, placed it in the current directory, set PGSERVICEFILE environment variable to point to it: [my_service] dbname = mydb host = myhost port = myhost is a different host than the one where the app runs. Unfortunately, the app could not connect to the intended database. It tried to connect to the (non-existent) database server on the local machine and failed. [Cause] ECPGconnect() parses the URI and produces an empty host name. It passes an empty string as the value for "host" connection parameter to PQconnectdbParams(). Given an empty host name, PQconnectdbParams() ignores the host parameter in pg_service.conf. When host is "", PQconnectdbParams() try to connect via local UNIX domain socket. [Fix] It doesn't make sense for ECPGconnect() to pass an empty host name to PQconnectdbParams(), so prevent it from passing host parameter for the service setting to take effect. port is the same. Regards MauMau ecpg_service.patch Description: Binary data -- 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] [bug fix] pg_ctl always uses the same event source
From: "Amit Kapila" Few minor things: 1. evtHandle = RegisterEventSource(NULL, *event_source? event_source: DEFAULT_EVENT_SOURCE); In this code, you are trying to access the value (*event_source) and incase it is not initialised, it will not contain the value and could cause problem, why not directly check 'event_source'? event_source here is a global static char array, so it's automatically initialized with zeros and safe to access. 2. minor coding style issue pg_ctl.c evtHandle = RegisterEventSource(NULL, *event_source? event_source: DEFAULT_EVENT_SOURCE); elog.c ! evtHandle = RegisterEventSource(NULL, ! event_source ? event_source : DEFAULT_EVENT_SOURCE); In both above usages, it is better that arguments in second line should start inline with previous lines first argument. You can refer other places, for ex. refer call to ReportEvent in pg_ctl.c just below RegisterEventSource call. Thanks. I passed the source files through pgindent and attached the revised patch. Although the arguments in the second line are not in line with the first line's arguments, that's what pgindent found good. Regards MauMau pg_ctl_eventsrc_v3.patch Description: Binary data -- 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] Optimize kernel readahead using buffer access strategy
Hi,
I fixed the patch to improve followings.
- Can compile in MacOS.
- Change GUC name enable_kernel_readahead to readahead_strategy.
- Change POSIX_FADV_SEQUNENTIAL to POISX_FADV_NORMAL when we select sequential
access strategy, this reason is later...
I tested simple two access paterns which are followings in pgbench tables scale
size is 1000.
A) SELECT count(bid) FROM pgbench_accounts; (Index only scan)
B) SELECT count(bid) FROM pgbench_accounts; (Seq scan)
In each test, I restart postgres and drop file cache before each test.
Unpatched PG is faster than patched in A and B query. It was about 1.3 times
faster. Result of A query as expected, because patched PG cannot execute
readahead at all. So cache cold situation is bad for patched PG. However, it
might good for cache hot situation, because it doesn't read disk IO at all and
can calculate file cache usage and know which cache is important.
However, result of B query as unexpected, because my patch select
POSIX_FADV_SEQUNENTIAL collectry, but it slow. I cannot understand that,
nevertheless I read kernel source code... Next, I change POSIX_FADV_SEQUNENTIAL
to POISX_FADV_NORMAL in my patch. B query was faster as unpatched PG.
In heavily random access benchmark tests which are pgbench and DBT-2, my patched
PG is about 1.1 - 1.3 times faster than unpatched PG. But postgres buffer hint
strategy algorithm have not optimized for readahead strategy yet, and I don't fix
it. It is still only for ring buffer algorithm in shared_buffer.
Attached printf-debug patch will show you inside postgres buffer strategy. When
you see "S" it selects sequential access strategy, on the other hands, when you
see "R" it selects random access strategy. It might interesting for you. It's
very visual.
Example output is here.
[mitsu-ko@localhost postgresql]$ bin/vacuumdb
SSS~~SS
[mitsu-ko@localhost postgresql]$ bin/psql -c "EXPLAIN ANALYZE SELECT count(aid) FROM
pgbench_accounts"
QUERY PLAN
-
Aggregate (cost=2854.29..2854.30 rows=1 width=4) (actual time=33.438..33.438
rows=1 loops=1)
-> Index Only Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.29..2604.29 rows=10 width=4) (actual time=0.072..20.912 rows=10
loops=1)
Heap Fetches: 0
Total runtime: 33.552 ms
(4 rows)
RRR~~RR
[mitsu-ko@localhost postgresql]$ bin/psql -c "EXPLAIN ANALYZE SELECT count(bid) FROM
pgbench_accounts"
SSS~~SS
--
Aggregate (cost=2890.00..2890.01 rows=1 width=4) (actual time=40.315..40.315
rows=1 loops=1)
-> Seq Scan on pgbench_accounts (cost=0.00..2640.00 rows=10 width=4)
(actual time=0.112..23.001 rows=10 loops=1)
Total runtime: 40.472 ms
(3 rows)
Thats's all now.
Regards,
--
Mitsumasa KONDO
NTT Open Source Software Center
*** a/configure
--- b/configure
***
*** 19937,19943 LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'`
! for ac_func in cbrt dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll pstat readlink setproctitle setsid shm_open sigprocmask symlink sync_file_range towlower utime utimes wcstombs wcstombs_l
do
as_ac_var=`$as_echo "ac_cv_func_$ac_func" | $as_tr_sh`
{ $as_echo "$as_me:$LINENO: checking for $ac_func" >&5
--- 19937,19943
! for ac_func in cbrt dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll posix_fadvise pstat readlink setproctitle setsid shm_open sigprocmask symlink sync_file_range towlower utime utimes wcstombs wcstombs_l
do
as_ac_var=`$as_echo "ac_cv_func_$ac_func" | $as_tr_sh`
{ $as_echo "$as_me:$LINENO: checking for $ac_func" >&5
*** a/doc/src/sgml/config.sgml
--- b/doc/src/sgml/config.sgml
***
*** 1252,1257 include 'filename'
--- 1252,1281
+
+ readahead_strategy (integer)
+
+readahead_strategyconfiguration parameter
+
+
+
+ This feature is to select which readahead strategy is used. When we
+ set off(default), readahead strategy is optimized by OS. On the other
+ hands, when we set on, readahead strategy is optimized by Postgres.
+ In typicaly situations, OS readahead strategy will be good working,
+ however Postgres often knows better readahead strategy before
+ executing disk access. For example, we can easy to predict access
+ pattern when we input SQLs, because planner of postgres decides
+ effici
Re: [HACKERS] Extension Templates S03E11
Tom Lane writes: > Right. I think a lot of the tension comes from people being unconvinced > that the existing extension feature is an ideal model for this sort of > use-case. Extensions were mainly designed around the notion of a .so The effort here is all about extending the Extension Use Case, yes. > OTOH, for a set of pure-SQL objects, it's not necessary that there be a > canonical text file somewhere, and we have in principle complete knowledge > of the objects' semantics as well as the ability to dump-and-restore into > newer PG versions. So it's not at all clear that we should just adopt the > existing model with the smallest possible changes --- which AFAICS is > basically what this proposal is. Maybe that's the way to go, but we > should consider alternatives, and in particular I think there is much > more reason to allow inside-the-database mutation of the SQL objects. My thinking is that if we invent a new mechanism for extensions that are not managed like contribs, we will find out that only contribs are going to be using extensions. Given the options of either growing extensions into being able to cope with more than a single model or building an entirely new system having most of the same feature set than Extensions, I'm pushing for the option where we build on top of what we have already. >> I think the name "Extension Templates" is horrible because it misleads >> all of us on this list into thinking the proposed feature is completely >> something other than what it is. I don't have a better name offhand, >> but that's got to change before it becomes a feature. > > Given your previous para, I wonder if "library" or "package" would work > better. I agree that "template" isn't le mot juste. We can't use “package” because it means something very different in direct competition. I have other propositions, but they are only relevant if we choose not to improve Extensions… right? Regards, -- Dimitri Fontaine06 63 07 10 78 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] [PATCH] Negative Transition Aggregate Functions (WIP)
On Mon, Dec 16, 2013 at 9:36 PM, Hannu Krosing wrote: > On 12/16/2013 08:39 AM, David Rowley wrote: > > > Any other ideas or +1's for any of the existing ones? > > +1, inverse good :) > > In the attached patch I've renamed negative to inverse. I've also disabled the inverse functions when an expression in an aggregate contains a volatile function. Regards David Rowley inverse_aggregate_functions_v1.0.patch.gz Description: GNU Zip compressed data -- 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] Changeset Extraction Interfaces
On 2013-12-16 23:01:16 -0500, Robert Haas wrote: > On Sat, Dec 14, 2013 at 12:37 PM, Andres Freund > wrote: > > On 2013-12-14 11:50:00 -0500, Robert Haas wrote: > >> Well, it still seems to me that the right way to think about this is > >> that the change stream begins at a certain point, and then once you > >> cross a certain threshold (all transactions in progress at that time > >> have ended) any subsequent snapshot is a possible point from which to > >> roll forward. > > > > Unfortunately it's not possible to build exportable snapshots at any > > time - it requires keeping far more state around since we need to care > > about all transactions, not just transactions touching the > > catalog. Currently you can only export the snapshot in the one point we > > become consistent, after that we stop maintaining that state. > > I don't get it. Once all the old transactions are gone, I don't see > why you need any state at all to build an exportable snapshot. Just > take a snapshot. The state we're currently decoding, somewhere in already fsynced WAL, won't correspond to the state in the procarray. There might be situations where it will, but we can't guarantee that we ever reach that point without taking locks that will be problematic. > The part that you're expressing willingness to do sounds entirely > satisfactory to me. As I mentioned on the other thread, I'm perhaps > even willing to punt that feature entirely provided that we have a > clear design for how to add it later, but I think it'd be nicer to get > it done now. We'll see how the next version looks like. Not sure on that myself yet ;) > And just for the record, I think the idea that I am holding this patch > hostage is absurd. I have devoted a large amount of time and energy > to moving this forward and plan to devote more. Because of that work, > big chunks of what is needed here are already committed. If my secret > plan is to make it as difficult as possible for you to get this > committed, I'm playing a deep game. I am not saying at all that you're planning to stop the patch from getting in. You've delivered pretty clear proof that that's not the case. But that doesn't prevent us from arguing over details and disagreeing whether they are dealbreakers or not, does it ;) I think you know that I am hugely grateful for the work you've put into the topic. Greetings, Andres Freund -- Andres Freund 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] Why no INSTEAD OF triggers on tables?
On 12/17/13, 4:53 AM, Robert Haas wrote: Well, I'm specifically thinking of master partition tables. In that case, we really want an INSTEAD OF trigger. /me scratches head. So, put a BEFORE trigger, and make it return NULL. Same effect, different notation. But it's not the same effect at all, that's the point: =# create view foov as select 1 as a; CREATE VIEW =# create function insteadof() returns trigger as $$ $# begin $# -- INSERT here $# return new; $# end $# $$ language plpgsql; CREATE FUNCTION =# create function before() returns trigger as $$ $# begin $# -- INSERT here $# return null; $# end $# $$ language plpgsql; CREATE FUNCTION =# create trigger t1 instead of insert on foov for each row execute procedure insteadof(); CREATE TRIGGER =# create trigger t2 before insert on bart for each row execute procedure before(); CREATE TRIGGER =# insert into foov values (1) returning *; a --- 1 (1 row) INSERT 0 1 local:marko=#* insert into bart values (1) returning *; a --- (0 rows) INSERT 0 0 Regards, Marko Tiikkaja -- 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] dpkg-buildpackage fails on 9.2.6 on ubuntu 12.04.3 LTS
Resolved. Regression test outputs for name and enum can be replaced with generated out. On Tue, Dec 17, 2013 at 12:17 AM, imagene...@gmail.com wrote: > This is resolved by running dpkg-buildpackage with postgres and > dpkg-buildpackage -rsudo with the postgresql source folder in a > directory owned by postgres as dpkg creates a temporary folder in said > parent directory. > > However, I am now running into the following issue: > > The reason I am compiling is because I must change #define NAMEDATALEN > 64 to a larger value. Setting it to 256 has presumably yielded the > following errors in the make check: > > It fails on the name and enum tests. > > I am rerunning the compilation to confirm this is a result of this > change. Please specify how to remove the regression check from the > dpkg build or how to resolve this (is there a max value that will not > fail?) if this is caused by this change or by something else on the > specified platform. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
