Re: [HACKERS] crash-safe visibility map, take three
On 2011-01-06 03:10, Robert Haas wrote: On Wed, Jan 5, 2011 at 3:22 PM, Jesper Krogh wrote: Given a crash-safe visibility map, what purpuse does the PD_ALL_VISIBLE bit serve? If we modify a page on which PD_ALL_VISIBLE isn't set, we don't attempt to update the visibility map. In theory, this is an important optimization to reduce contention on the visibility map page, since there are something like 64K heap pages per visibility map page. In practice, I'm not sure in what workloads it matters or by how much If that is the only reason, I would suggest just making the visibillity map pages more sparse. If you just had 500 or 1K heap pages per visibillity map page, then it would not change a thing for the performance. With 1K heap pages per VM map page a relation with 100GB of data would have a VM map of the VM map of 100MB, even 100 heap pages per VM-map page would still enable the database to have visibillity information of 100GB data stored in 1GB of memory. But it would need testing and benchmarking to find out. Jesper -- Jesper -- 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] sepgsql contrib module
(2011/01/06 14:28), Robert Haas wrote: > 2011/1/5 KaiGai Kohei: >> The attached patch is the modular version of SE-PostgreSQL (take.2). > > I'm reading through the documentation and so far it looks pretty > reasonable. But I have some questions and suggested changes, of > course. :-) > Thanks for your reviewing in spite of large chunk. > 1. Why is sepgsql the right name for this module, as opposed to, say, > selinux? We don't call the cube module cubepgsql, or the hstore > module hstorepgsql. Maybe there's a reason why this case is > different, but I'm not sure. > In some previous cases when SELinux model was ported to other systems, its project was named as SE-(other system), such as SE-BSD, SE-X, etc... I named it according to this convention, however, it is indeed uncertain whether 'sepgsql' follows on the convention in pgsql side. I don't think it is a strong reason why the module is named as 'sepgsql' instead of 'selinux'. In advertisement context, we can just call it as SE-PostgreSQL. > 2. The docs contains some references to /usr/local/pgsql/share.. Does > this really mean "whatever sharedir you established when you ran > configure", i.e. the output of pg_config --sharedir? I hope so. > Yes, it means the sharedir being configured. I found the following description at the installation.sgml. I should put this kind of mention on the documentation. | | These instructions assume that your existing installation is under the | /usr/local/pgsql directory, and that the data area is in | /usr/local/pgsql/data. Substitute your paths | appropriately. | > 3. The language for the sepgsql.permissive GUC suggests that it's > PGC_POSTMASTER, but I'd think PGC_SIGHUP ought to be sufficient. > Either way, please copy the appropriate language from some existing > GUC of the same type instead of inventing a new way to say it. I also > have no idea what "because it invalidates all the inefficient stuff" > means. > OK, I'll try to find up similar description then fix up both of the code and documentation. > 4. Please remove the upcoming features section of the documentation. > This material is appropriate for a page on the wiki, but shouldn't be > part of the official documentation. Instead, you might want to have a > *short* "Limitations" section. > OK, I'll replace an itemization of limitations in this version. > 5. I'm not too sure about this one, but I think it might be good to > elaborate on what we mean by respecting the system SE-Linux policy. > What kinds of objects do we support checks on? What sorts of checks? > What kind of access can we allow/deny? > I guess these detailed description makes amount of this chapter disproportionally increase in the future version. My preference is wikipage to provide this kind of detailed information. http://wiki.postgresql.org/wiki/SEPostgreSQL The contents of above wikipage is now obsoleted, because it assumed SELinux support as a built-in feature. But it is a good time to fix up the description. Thanks, -- KaiGai Kohei -- 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] Avoiding rewrite in ALTER TABLE ALTER TYPE
On Thu, Jan 06, 2011 at 12:24:19AM -0500, Robert Haas wrote: > I still think you're better off focusing first on the case where we > can skip the whole nine yards, and doing this stuff as a follow-on > patch. Trying to do too many things, especially possibly > controversial stuff, especially in the last CommitFest, often ends up > with the whole patch getting rejected, which makes no one happy. > Submitting the smallest useful, self-contained change you can and then > work up from there. Or at least split out the patch into parts that > can be applied independently, so that if the eventual committer likes > A but not B you at least get A in. Will do. Thanks. -- 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] sepgsql contrib module
2011/1/5 KaiGai Kohei : > The attached patch is the modular version of SE-PostgreSQL (take.2). I'm reading through the documentation and so far it looks pretty reasonable. But I have some questions and suggested changes, of course. :-) 1. Why is sepgsql the right name for this module, as opposed to, say, selinux? We don't call the cube module cubepgsql, or the hstore module hstorepgsql. Maybe there's a reason why this case is different, but I'm not sure. 2. The docs contains some references to /usr/local/pgsql/share.. Does this really mean "whatever sharedir you established when you ran configure", i.e. the output of pg_config --sharedir? I hope so. 3. The language for the sepgsql.permissive GUC suggests that it's PGC_POSTMASTER, but I'd think PGC_SIGHUP ought to be sufficient. Either way, please copy the appropriate language from some existing GUC of the same type instead of inventing a new way to say it. I also have no idea what "because it invalidates all the inefficient stuff" means. 4. Please remove the upcoming features section of the documentation. This material is appropriate for a page on the wiki, but shouldn't be part of the official documentation. Instead, you might want to have a *short* "Limitations" section. 5. I'm not too sure about this one, but I think it might be good to elaborate on what we mean by respecting the system SE-Linux policy. What kinds of objects do we support checks on? What sorts of checks? What kind of access can we allow/deny? -- 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] Avoiding rewrite in ALTER TABLE ALTER TYPE
On Wed, Jan 5, 2011 at 11:26 PM, Noah Misch wrote: > On Thu, Dec 30, 2010 at 08:35:34PM -0500, Noah Misch wrote: >> On Thu, Dec 30, 2010 at 12:57:45AM -0500, Robert Haas wrote: >> > On Thu, Dec 30, 2010 at 12:24 AM, Noah Misch wrote: >> > > On Wed, Dec 29, 2010 at 11:14:37PM -0500, Robert Haas wrote: >> > >> I think for any pair of types (T1, T2) we should first determine >> > >> whether we can skip the scan altogether. ?If yes, we're done. ?If no, >> > >> then we should have a way of determining whether a verify-only scan is >> > >> guaranteed to be sufficient (in your terminology, the verification >> > >> scan is guaranteed to return either positive or error, not negative). >> > >> If yes, then we do a verification scan. ?If no, we do a rewrite. >> > > >> > > How would we answer the second question in general? >> > >> > I am not sure - I guess we'd need to design some sort of mechanism for >> > that. >> >> Okay, here goes. Given... > > That seems to be working decently. However, It turns out that changes like > text->varchar(8) and varchar(8)->varchar(4) don't fall into either of those > optimization categories. An implicit varchar length coercion will truncate > trailing blanks to make the string fit, so this legitimately requires a > rewrite: > > CREATE TEMP TABLE t (c) AS SELECT 'foo '::text; > SELECT c || '<-' FROM t; > ALTER TABLE t ALTER c TYPE varchar(4); > SELECT c || '<-' FROM t; > > In light of that, I'm increasingly thinking we'll want a way for the user to > request a scan in place of a rewrite. The scan would throw an error if a > rewrite ends up being necessary. Adding a keyword for that purpose, the > syntax > would resemble: > > ALTER TABLE ALTER [COLUMN] [SET DATA] TYPE > [IMPLICIT] [ USING ] > > I had wished to avoid this as something of a UI wart, but I don't see a way to > cover all important conversions automatically and with a single-pass > guarantee. > This would cover the rest. > > Thoughts? I still think you're better off focusing first on the case where we can skip the whole nine yards, and doing this stuff as a follow-on patch. Trying to do too many things, especially possibly controversial stuff, especially in the last CommitFest, often ends up with the whole patch getting rejected, which makes no one happy. Submitting the smallest useful, self-contained change you can and then work up from there. Or at least split out the patch into parts that can be applied independently, so that if the eventual committer likes A but not B you at least get A in. Don't take any of this as a rejection of any of what you're proposing; I haven't really made up my mind yet, and there are plenty of other people who would have a say even if I had. Rather, I'd like to maximize the chances of us at least part of this work committed to 9.1. -- 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] Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
On Wed, Jan 5, 2011 at 11:08 PM, Tom Lane wrote: > Or we could take the approach somebody was just espousing about > >> Our job is to prevent the user from *accidentally* >> shooting themselves in the foot. I don't see how you can compare those two cases with a straight face. In the FOREIGN KEY NOT ENFORCED case, there is an explicit piece of syntax by means of which the user is asking for the dangerous behavior. In this case, the user made a settings change which was allowed by the system and solved his problem, and then pg_upgrade broke. If he had typed ALTER DATABASE .. SET ROLE .. BREAK PG_UPGRADE, the two cases would be comparable. Or if we failed to enforce foreign keys by default, that'd be comparable, too. How exactly is the user supposed to know that ALTER DATABASE .. SET ROLE is a bad idea? You've repeatedly made remarks about "deliberately hosing the login system", but you've offered no evidence that the user "deliberately hosed" anything. Changed the behavior? Well, yeah. And fixed his problem, too! I even sympathize with his use case. Hosed? Well, maybe. It worked for him, until he tried to run pg_upgrade. Deliberately hosed, like he did it just to break things? Doesn't seem that way. Your argument rests on the presumption that the user should have known better than to execute a command which didn't produce an error and did solve his problem. Perhaps that's a reasonable argument in some cases - a user might be reasonably expected to foresee that setting work_mem to 100GB could cause problems even if it happens to fix the immediate issue, based on the description of the parameter - but neither you nor anyone else on this thread have offered more than hand-waving to explain how the user was supposed to know that it was unwise, or even to substantiate your position that it WAS unwise. -- 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] Streaming replication as a separate permissions
On tis, 2011-01-04 at 22:24 -0500, Robert Haas wrote: > > Just to be clear: are we saying that "CREATE ROLE foo SUPERUSER" > > should grant both superuser and replication, as well as the default > > "postgres" user also having replication as well? > > I think that's what we're saying. So now superusers have it by default but you can explicitly revoke it? I guess that's still inconsistent with other superuser behavior. You can't revoke a superuser's CREATEDB bit, for example. (You can, but it won't have an effect, of course.) -- 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] Streaming replication as a separate permissions
On mån, 2011-01-03 at 11:20 -0500, Tom Lane wrote: > You might want to reflect on rolcatupdate a bit before asserting that > there are no cases where privileges are ever denied to superusers. Arguably, the reason that that is hardly documented and slightly deprecated is that the underlying design decision is questionable. -- 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] Avoiding rewrite in ALTER TABLE ALTER TYPE
On Thu, Dec 30, 2010 at 08:35:34PM -0500, Noah Misch wrote: > On Thu, Dec 30, 2010 at 12:57:45AM -0500, Robert Haas wrote: > > On Thu, Dec 30, 2010 at 12:24 AM, Noah Misch wrote: > > > On Wed, Dec 29, 2010 at 11:14:37PM -0500, Robert Haas wrote: > > >> I think for any pair of types (T1, T2) we should first determine > > >> whether we can skip the scan altogether. ?If yes, we're done. ?If no, > > >> then we should have a way of determining whether a verify-only scan is > > >> guaranteed to be sufficient (in your terminology, the verification > > >> scan is guaranteed to return either positive or error, not negative). > > >> If yes, then we do a verification scan. ?If no, we do a rewrite. > > > > > > How would we answer the second question in general? > > > > I am not sure - I guess we'd need to design some sort of mechanism for that. > > Okay, here goes. Given... That seems to be working decently. However, It turns out that changes like text->varchar(8) and varchar(8)->varchar(4) don't fall into either of those optimization categories. An implicit varchar length coercion will truncate trailing blanks to make the string fit, so this legitimately requires a rewrite: CREATE TEMP TABLE t (c) AS SELECT 'foo '::text; SELECT c || '<-' FROM t; ALTER TABLE t ALTER c TYPE varchar(4); SELECT c || '<-' FROM t; In light of that, I'm increasingly thinking we'll want a way for the user to request a scan in place of a rewrite. The scan would throw an error if a rewrite ends up being necessary. Adding a keyword for that purpose, the syntax would resemble: ALTER TABLE ALTER [COLUMN] [SET DATA] TYPE [IMPLICIT] [ USING ] I had wished to avoid this as something of a UI wart, but I don't see a way to cover all important conversions automatically and with a single-pass guarantee. This would cover the rest. Thoughts? -- 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 pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
On 01/05/2011 11:08 PM, Tom Lane wrote: If they want to deliberately shoot themselves in the foot by hosing the login system like that, it's not our job to prevent it. But it's not our job to try to work around it, either. I think this is especially true in this case, when the problem is easily and quickly worked around by the end user. 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] Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
Tom Lane wrote: > Robert Haas writes: > > On Wed, Jan 5, 2011 at 9:44 PM, Bruce Momjian wrote: > >> I think pg_dumpall would have failed with this setup too, so I don't see > >> this as a pg_upgrade bug, nor something that I am willing to risk adding > >> to pg_upgrade. > > > If adding RESET SESSION AUTHORIZATION fixes the bug, I think we should > > consider doing that. > > I think an appropriate response would be to prevent ALTER DATABASE SET > ROLE. I really cannot believe that there are any situations where > that's a good idea. > > Or we could take the approach somebody was just espousing about > > > Our job is to prevent the user from *accidentally* > > shooting themselves in the foot. > > If they want to deliberately shoot themselves in the foot by hosing the > login system like that, it's not our job to prevent it. But it's not > our job to try to work around it, either. Yep. We should probably make a decision on foot-guns and be consistent, at least. Doing it half-way isn't helping anyone. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
Robert Haas writes: > On Wed, Jan 5, 2011 at 9:44 PM, Bruce Momjian wrote: >> I think pg_dumpall would have failed with this setup too, so I don't see >> this as a pg_upgrade bug, nor something that I am willing to risk adding >> to pg_upgrade. > If adding RESET SESSION AUTHORIZATION fixes the bug, I think we should > consider doing that. I think an appropriate response would be to prevent ALTER DATABASE SET ROLE. I really cannot believe that there are any situations where that's a good idea. Or we could take the approach somebody was just espousing about > Our job is to prevent the user from *accidentally* > shooting themselves in the foot. If they want to deliberately shoot themselves in the foot by hosing the login system like that, it's not our job to prevent it. But it's not our job to try to work around it, either. 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] Problem with pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
Robert Haas wrote: > On Wed, Jan 5, 2011 at 9:44 PM, Bruce Momjian wrote: > > I think pg_dumpall would have failed with this setup too, so I don't see > > this as a pg_upgrade bug, nor something that I am willing to risk adding > > to pg_upgrade. > > If adding RESET SESSION AUTHORIZATION fixes the bug, I think we should > consider doing that. If we add every fix that could conceivably break a pg_dumpall restore, pg_upgrade will be less stable than it is now. I don't see why adding this should be any different. If you want to argue that pg_dumpall should be doing it, that is a separate issue and not related to pg_upgrade. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
On Wed, Jan 5, 2011 at 9:44 PM, Bruce Momjian wrote: > I think pg_dumpall would have failed with this setup too, so I don't see > this as a pg_upgrade bug, nor something that I am willing to risk adding > to pg_upgrade. If adding RESET SESSION AUTHORIZATION fixes the bug, I think we should consider doing that. -- 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] ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
Robert Haas wrote: > On Mon, Dec 13, 2010 at 12:59 AM, Rob Wultsch wrote: > > On Sun, Dec 12, 2010 at 7:24 PM, Andrew Dunstan wrote: > >> In fact it's possible now to disable FK enforcement, by disabling the > >> triggers. It's definitely a footgun though. Just the other day I was asked > >> how data violating the constraint could have got into the table, and caused > >> some surprise by demonstrating how easy this was to produce. > > > > Ugh. I have read the entire pg manual and I did not recall that > > footgun. ?At least in MySQL disabling fk's is explicit. There is > > something to be said for being able to tell the database: "Hey, hold > > my beer and watch this, it might be stupid but it is what we are going > > to do". > > I couldn't agree more, and that's a great way to put it. The user is > in charge. Our job is to prevent the user from *accidentally* > shooting themselves in the foot. But if a crocodile is biting their > foot off and they want to fire their gun in that direction and take > their chances, it's not our job to say "oh, no, you might injure your > foot". DBAs hate getting eaten by crocodiles. Is this a TODO? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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 pg_upgrade (8.4 -> 9.0) due to ALTER DATABASE SET ROLE
Florian Pflug wrote: > Hi > > I've just ran into a problem while upgrading from 8.4 to 9.0. > > pg_upgrade aborted during the step "Adding support functions to new > cluster" with "ERROR: permission denied for language c" error. > Unfortunately, the log didn't include the name of the database where > the error occurred, so it took me a while to figure out that the culprit > was a "ALTER DATABASE SET ROLE = " I had done on one of > my databases, which effectively prevented pg_upgrade from connection > with superuser privileges. > > While one could argue that this behaviour is perfectly consistent, I > believe most users will expect pg_upgrade (and to a lesser extent also > pg_dump and pg_restore) to be unaffected by such settings. > > Should we provide a way (for super-users only, of course) to skip > per-database/per-role settings when connecting? I think pg_dumpall would have failed with this setup too, so I don't see this as a pg_upgrade bug, nor something that I am willing to risk adding to pg_upgrade. Perhaps the error report can be improved. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What is lo_insert?
Robert Haas wrote:
> On Wed, Jan 5, 2011 at 12:21 PM, Bruce Momjian wrote:
> > The psql manual page mentions lo_insert:
> >
> > ? ? ? ? ? LASTOID
> > ? ? ? ? ? ? ? The value of the last affected OID, as returned
> > --> ? ? ? ? ? ?from an INSERT or lo_insert command. This variable
> > ? ? ? ? ? ? ? is only guaranteed to be valid until after the
> > ? ? ? ? ? ? ? result of the next SQL command has been displayed.
> >
> > Should that be lo_import? ?I don't see lo_insert used anywhere. ?And it
> > doesn't seem lo_import sets LASTOID.
>
> I think it's supposed to be lo_import. It sets LASTOID if you do this:
>
> rhaas=# \lo_import '/etc/passwd'
> lo_import 16414
> rhaas=# select :LASTOID;
> ?column?
> --
> 16414
> (1 row)
OK, but it does not set :LASTOID if you do this:
SELECT lo_import('/etc/motd');
I have updated the patch to say '\lo_import' and backpatched to 9.0.X.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 0c658d1..5f61561 100644
*** /tmp/pgdiff.3068/TTCobb_psql-ref.sgml Wed Jan 5 21:29:31 2011
--- doc/src/sgml/ref/psql-ref.sgml Wed Jan 5 21:29:28 2011
*** bar
*** 2679,2685
The value of the last affected OID, as returned from an
! INSERT or lo_insert
command. This variable is only guaranteed to be valid until
after the result of the next SQL command has
been displayed.
--- 2679,2685
The value of the last affected OID, as returned from an
! INSERT or \lo_import
command. This variable is only guaranteed to be valid until
after the result of the next SQL command has
been displayed.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fix for pg_upgrade migrating pg_largeobject_metadata
Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian writes:
> > > Tom Lane wrote:
> > >> That isn't going to work. At least not unless you start trying to force
> > >> roles to have the same OIDs in the new installation.
> >
> > > If so I can use the CREATE ROLE ... SYSID clause when doing a binary
> > > upgrade.
> >
> > Oh, I had forgotten we still had that wart in the grammar.
> > It doesn't actually work:
> >
> > else if (strcmp(defel->defname, "sysid") == 0)
> > {
> > ereport(NOTICE,
> > (errmsg("SYSID can no longer be
> > specified")));
> > }
> >
> > Not sure if it's better to try to make that work again than to add
> > another hack in pg_upgrade_support. On the whole that's a keyword
> > I'd rather see us drop someday soon.
>
> OK, let me work on adding it to pg_upgrade_support. Glad you saw this.
I have fixed the bug by using pg_upgrade_support. It was a little
complicated because you need to install the pg_upgrade_support functions
in the super-user database so it is available when you create the users
in the first step of restoring the pg_dumpall file.
I am afraid we have to batckpatch this to fix to 9.0 for 9.0 to 9.0
upgrades. It does not apply when coming from pre-9.0 because there was
no pg_largeobject_metadata.
For testing I did this:
CREATE DATABASE lo;
\c lo
SELECT lo_import('/etc/motd');
\set loid `psql -qt -c 'select loid from pg_largeobject' lo`
CREATE ROLE user1;
CREATE ROLE user2;
-- force user2 to have a different user id on restore
DROP ROLE user1;
GRANT ALL ON LARGE OBJECT :loid TO user2;
The fixed version shows:
lo=> select * from pg_largeobject_metadata;
lomowner | lomacl
--+--
10 | {postgres=rw/postgres,user2=rw/postgres}
(1 row)
In the broken version, 'user2' was a raw oid, obviously wrong.
Fortunately this was found during my testing and not reported as a bug
by a pg_upgrade user.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/dump.c b/contrib/pg_upgrade/dump.c
index 52ab481..aba95f4 100644
*** /tmp/pgdiff.20347/YPWaqb_dump.c Wed Jan 5 20:52:07 2011
--- contrib/pg_upgrade/dump.c Wed Jan 5 20:43:29 2011
*** generate_old_dump(void)
*** 33,39
*
*This function splits pg_dumpall output into global values and
*database creation, and per-db schemas. This allows us to create
! *the toast place holders between restoring these two parts of the
*dump. We split on the first "\connect " after a CREATE ROLE
*username match; this is where the per-db restore starts.
*
--- 33,39
*
*This function splits pg_dumpall output into global values and
*database creation, and per-db schemas. This allows us to create
! *the support functions between restoring these two parts of the
*dump. We split on the first "\connect " after a CREATE ROLE
*username match; this is where the per-db restore starts.
*
diff --git a/contrib/pg_upgrade/function.c b/contrib/pg_upgrade/function.c
index 2ab8e4f..5675551 100644
*** /tmp/pgdiff.20347/QpVBHa_function.c Wed Jan 5 20:52:07 2011
--- contrib/pg_upgrade/function.c Wed Jan 5 20:26:33 2011
***
*** 13,35
/*
! * install_support_functions()
*
* pg_upgrade requires some support functions that enable it to modify
* backend behavior.
*/
void
! install_support_functions(void)
{
! int dbnum;
!
! prep_status("Adding support functions to new cluster");
!
! for (dbnum = 0; dbnum < new_cluster.dbarr.ndbs; dbnum++)
! {
! DbInfo *new_db = &new_cluster.dbarr.dbs[dbnum];
! PGconn *conn = connectToServer(&new_cluster,
new_db->db_name);
!
/* suppress NOTICE of dropped objects */
PQclear(executeQueryOrDie(conn,
"SET
client_min_messages = warning;"));
--- 13,28
/*
! * install_db_support_functions()
*
* pg_upgrade requires some support functions that enable it to modify
* backend behavior.
*/
void
! install_db_support_functions(const char *db_name)
{
! PGconn *conn = connectToServer(&new_cluster, db_name);
!
/* suppress NOTICE of dropped objects */
PQclear(executeQueryOrDie(conn,
"SET
client_min_messages = warning;"));
*** install_support_functions(void)
*** 83,91
"RETURNS
Re: [HACKERS] What is lo_insert?
On Wed, Jan 5, 2011 at 12:21 PM, Bruce Momjian wrote: > The psql manual page mentions lo_insert: > > LASTOID > The value of the last affected OID, as returned > --> from an INSERT or lo_insert command. This variable > is only guaranteed to be valid until after the > result of the next SQL command has been displayed. > > Should that be lo_import? I don't see lo_insert used anywhere. And it > doesn't seem lo_import sets LASTOID. I think it's supposed to be lo_import. It sets LASTOID if you do this: rhaas=# \lo_import '/etc/passwd' lo_import 16414 rhaas=# select :LASTOID; ?column? -- 16414 (1 row) -- 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] crash-safe visibility map, take three
On Wed, Jan 5, 2011 at 3:22 PM, Jesper Krogh wrote: > Given a crash-safe visibility map, what purpuse does the PD_ALL_VISIBLE bit > serve? If we modify a page on which PD_ALL_VISIBLE isn't set, we don't attempt to update the visibility map. In theory, this is an important optimization to reduce contention on the visibility map page, since there are something like 64K heap pages per visibility map page. In practice, I'm not sure in what workloads it matters or by how much. -- 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] making an unlogged table logged
On Wed, Jan 5, 2011 at 6:25 PM, Josh Berkus wrote: > On 1/5/11 3:14 PM, Robert Haas wrote: >> I think that's probably a dead end - just to take one example, if you >> don't sync often enough, the standby might have transaction ID >> wraparound problems. Autovacuum on the master will prevent that for >> permanent tables, but not for an only-occasionally-updated copy of an >> unlogged table. > > I think you're missing Agent M's idea: if you could write to unlogged > tables on the standby, then you could use application code to > periodically synch them. > > Mind you, I personally don't find that idea that useful -- unlogged > tables are supposed to be for highly volatile data, after all. No doubt > M was thinking that in a failover situation, it would be better to have > stale data than none at all. > > However, if an unlogged table created on the master could be available > for writing and initially empty on the standbys, it would give each > standby available temporary/buffer tables they could use. That would be > *really* useful. OIC, sorry. Well, that could possibly be done, but it'd be tricky. The obvious problem is that the backend doing the writing would need an XID, and it'd probably have to ask the master to assign it one... which is possibly doable, but certainly not ideal (you can't write on the slave if the master is down, unless you promote it). Then there's a whole bunch of follow-on problems, like now the standby needs to run autovacuum - but only on the unlogged tables, and without being able to update or rely on pg_database.datfrozenxid. I think we have to face up to the fact that WAL shipping is an extremely limiting way to do replication. It has its perks, certainly - principally, that it minimizes the amount of extra work that must be done on the master, which is an extremely valuable consideration for many applications. However, it's also got some pretty major disadvantages, and one of the big ones is that it's not well-suited to partial replication. If it were possible to replicate individual tables, we wouldn't be having this conversation. You'd just replicate some tables from the master to the standby and then create a few extra ones on the standby (perhaps permanent, perhaps unlogged, perhaps temporary) and call it good. I think we ought to seriously consider having both physical and logical replication in core. Physical replication, which we have today, is great for what it does, but trying to make it do things that it's not good at is going to be an awful lot of work, and require an awful lot of hacks, to make it cover everything that people really want to be able to do. Adding logical replication would be a lot of work but we'd get a lot of collateral benefits. Imagine that PG had the ability to spit out INSERT/UPDATE/DELETE statements for designated tables, as they were modified. That would provide a framework for partial replication, replication from PG into other databases, even multi-master replication if you add some kind of conflict resolution. Even though this would require essentially building a whole new system, it's starting to seem to me that it would be simpler than trying to remove the limitations of our existing system incrementally. -- 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] WIP: Range Types
2011/1/6 Jeff Davis : > Even if add and subtract were associated with a range type, there's no > way to tell which range type to pick given the window function syntax > (multiple range types could be defined over the same subtype). > > I think the interface question should be addressed more directly with a > "type interfaces" patch. I agree the current canonical approach fits range type's demand, and I'm inclined that the type interface discussion is another point. IIRC, Robert Haas originally began to propose the idea of type interface to get together three of KNN-GIST, range type and window frame issue. For KNN-GIST, it was committed by extending pg_amop without considering others and range type will be as well. Not getting them together might be the answer. Regards, -- Hitoshi Harada -- 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] "writable CTEs"
Hello, I have added my common table expressions docpatch to the 2011-01 commitfest: https://commitfest.postgresql.org/action/patch_view?id=476 I think that we need to get this resolved. -- Regards, Peter Geoghegan -- 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] making an unlogged table logged
On 1/5/11 3:14 PM, Robert Haas wrote: > I think that's probably a dead end - just to take one example, if you > don't sync often enough, the standby might have transaction ID > wraparound problems. Autovacuum on the master will prevent that for > permanent tables, but not for an only-occasionally-updated copy of an > unlogged table. I think you're missing Agent M's idea: if you could write to unlogged tables on the standby, then you could use application code to periodically synch them. Mind you, I personally don't find that idea that useful -- unlogged tables are supposed to be for highly volatile data, after all. No doubt M was thinking that in a failover situation, it would be better to have stale data than none at all. However, if an unlogged table created on the master could be available for writing and initially empty on the standbys, it would give each standby available temporary/buffer tables they could use. That would be *really* useful. Also, one of the obvious uses for unlogged tables is materialized views. If unlogged tables don't get replicated, and can't be created on the standby, then it severely limits their utility for this purpose. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] making an unlogged table logged
On Wed, Jan 5, 2011 at 3:58 PM, Josh Berkus wrote: >> Hm- if the unlogged tables are being used as HTTP transient state storage, >> it would be handy to have that (admittedly non-essential) data on the >> standby when it becomes master, even if there are no guarantees surrounding >> the data beyond "it looked like this at some point". Since the tables are >> not writing WAL, would it be possible to allow for writing to unlogged >> tables on the standby to allow for out-of-band syncing? Otherwise, it seems >> the only alternative is to push changes to a separate database on the >> standby machine and then suck the data in when it becomes master. > > It would be useful (this would allow per-standby buffer tables, for that > matter), but it would also be tremendously difficult. Seems worthy of a > TODO, though. I think that's probably a dead end - just to take one example, if you don't sync often enough, the standby might have transaction ID wraparound problems. Autovacuum on the master will prevent that for permanent tables, but not for an only-occasionally-updated copy of an unlogged table. If what you want is a possibly-slightly-stale copy of a fast table on the master, there are ways to engineer that using technology we already have, such as asynchronous replication + Hot Standby + synchronous_commit=off, or by using Slony. Admittedly, none of these solutions are quite perfect, but it would probably be much easier to improve those solutions or develop something completely new rather than try to somehow go through the mammoth contortions that would be needed to make it work with unlogged tables. -- 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] We need to log aborted autovacuums
> This is a great use case for user level tracing support. Add a probe > around these bits, and you can capture the information when you need > it. Yeah, would be lovely if user-level tracing existed on all platforms. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] We need to log aborted autovacuums
On Wed, Jan 5, 2011 at 2:27 PM, Josh Berkus wrote: > >> If you could gather more info on whether this logging catches the >> problem cases you're seeing, that would really be the right test for the >> patch's usefulness. I'd give you solid 50/50 odds that you've correctly >> diagnosed the issue, and knowing for sure would make advocating for this >> logging a pretty easy sell to me at least. > > Well, I already resolved the issue through polling pg_locks. The issue > was IIT sessions which lasted up to an hour, which we fixed in the > application, so I don't have the test case available anymore. I'd have > to generate a synthetic test case, and since I agree that a SQL-callable > diagnostic is superior to logging, I don't think we should pursue the > log levels further. > This is a great use case for user level tracing support. Add a probe around these bits, and you can capture the information when you need it. Robert Treat http://www.xzilla.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] Streaming base backups
Magnus Hagander writes: >> Compression in libpq would be a nice way to solve it, later. > > Yeah, I'm pretty much set on postponing that one. +1, in case it was not clear for whoever's counting the votes :) >> What about pg_streamrecv | gzip > …, which has the big advantage of > > That's part of what I meant with "easier and more useful". Well… > Right now though, pg_streamrecv will output one tar file for each > tablespace, so you can't get it on stdout. But that can be changed of > course. The easiest step 1 is to just use gzopen() from zlib on the > files and use the same code as now :-) Oh if integrating it is easier :) >> Maybe have a look at pgfincore to only tag DONTNEED for blocks that are >> not already in SHM? > > I think that's way more complex than we want to go here. Yeah. >> Well, I would guess that if you're streaming the WAL files in parallel >> while the base backup is taken, then you're able to have it all without >> archiving setup, and the server could still recycling them. > > Yes, this was mostly for the use-case of "getting a single tarfile > that you can actually use to restore from without needing the log > archive at all". It also allows for a simpler kick-start procedure for preparing a standby, and allows to stop worrying too much about wal_keep_segments and archive servers. When do the standby launch its walreceiver? It would be extra-nice for the base backup tool to optionally continue streaming WALs until the standby starts doing it itself, so that wal_keep_segments is really deprecated. No idea how feasible that is, though. 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] making an unlogged table logged
Josh Berkus writes: > It would be useful (this would allow per-standby buffer tables, for that > matter), but it would also be tremendously difficult. Seems worthy of a > TODO, though. Don't we have anything covering xid-less tables in the TODO already? The read-only tables "compressions" of removing large part of headers seems to come up often enough… 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] Visual Studio 2010/Windows SDK 7.1 support
Robert Haas writes: > diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c > index 6a9b21d..1c6ae02 100644 > > I actually haven't figured out exactly under what circumstances that I think it's for git am -3 http://www.kernel.org/pub/software/scm/git/docs/git-am.html But I don't see how to use that on a patch directly, the UI seems to expect a mail file (mbox or Maildir formats). 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] Streaming base backups
On Wed, Jan 5, 2011 at 22:58, Dimitri Fontaine wrote: > Magnus Hagander writes: >> Attached is an updated streaming base backup patch, based off the work > > Thanks! :) > >> * Compression: Do we want to be able to compress the backups server-side? Or >> defer that to whenever we get compression in libpq? (you can still tunnel >> it >> through for example SSH to get compression if you want to) My thinking is >> defer it. > > Compression in libpq would be a nice way to solve it, later. Yeah, I'm pretty much set on postponing that one. >> * Compression: We could still implement compression of the tar files in >> pg_streamrecv (probably easier, possibly more useful?) > > What about pg_streamrecv | gzip > …, which has the big advantage of > being friendly to *any* compression command line tool, whatever patents > and licenses? That's part of what I meant with "easier and more useful". Right now though, pg_streamrecv will output one tar file for each tablespace, so you can't get it on stdout. But that can be changed of course. The easiest step 1 is to just use gzopen() from zlib on the files and use the same code as now :-) >> * Stefan mentiond it might be useful to put some >> posix_fadvise(POSIX_FADV_DONTNEED) >> in the process that streams all the files out. Seems useful, as long as >> that >> doesn't kick them out of the cache *completely*, for other backends as >> well. >> Do we know if that is the case? > > Maybe have a look at pgfincore to only tag DONTNEED for blocks that are > not already in SHM? I think that's way more complex than we want to go here. >> * include all the necessary WAL files in the backup. This way we could >> generate >> a tar file that would work on it's own - right now, you still need to set >> up >> log archiving (or use streaming repl) to get the remaining logfiles from >> the >> master. This is fine for replication setups, but not for backups. >> This would also require us to block recycling of WAL files during the >> backup, >> of course. > > Well, I would guess that if you're streaming the WAL files in parallel > while the base backup is taken, then you're able to have it all without > archiving setup, and the server could still recycling them. Yes, this was mostly for the use-case of "getting a single tarfile that you can actually use to restore from without needing the log archive at all". -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Streaming base backups
Magnus Hagander writes: > Attached is an updated streaming base backup patch, based off the work Thanks! :) > * Compression: Do we want to be able to compress the backups server-side? Or > defer that to whenever we get compression in libpq? (you can still tunnel it > through for example SSH to get compression if you want to) My thinking is > defer it. Compression in libpq would be a nice way to solve it, later. > * Compression: We could still implement compression of the tar files in > pg_streamrecv (probably easier, possibly more useful?) What about pg_streamrecv | gzip > …, which has the big advantage of being friendly to *any* compression command line tool, whatever patents and licenses? > * Stefan mentiond it might be useful to put some > posix_fadvise(POSIX_FADV_DONTNEED) > in the process that streams all the files out. Seems useful, as long as that > doesn't kick them out of the cache *completely*, for other backends as well. > Do we know if that is the case? Maybe have a look at pgfincore to only tag DONTNEED for blocks that are not already in SHM? > * include all the necessary WAL files in the backup. This way we could > generate > a tar file that would work on it's own - right now, you still need to set up > log archiving (or use streaming repl) to get the remaining logfiles from the > master. This is fine for replication setups, but not for backups. > This would also require us to block recycling of WAL files during the > backup, > of course. Well, I would guess that if you're streaming the WAL files in parallel while the base backup is taken, then you're able to have it all without archiving setup, and the server could still recycling them. 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] can shared cache be swapped to disk?
Jeff Janes writes:
> ("top" doesn't distinguish between memory that has been requested but
> never accessed, versus memory that has been accessed and then truly
> swapped out to disk. So unless you first let it run to steady-state
> before applying pressure, it is hard to interpret the results.)
Would exmap be helping you here?
http://www.berthels.co.uk/exmap/
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] making an unlogged table logged
> Hm- if the unlogged tables are being used as HTTP transient state storage, it > would be handy to have that (admittedly non-essential) data on the standby > when it becomes master, even if there are no guarantees surrounding the data > beyond "it looked like this at some point". Since the tables are not writing > WAL, would it be possible to allow for writing to unlogged tables on the > standby to allow for out-of-band syncing? Otherwise, it seems the only > alternative is to push changes to a separate database on the standby machine > and then suck the data in when it becomes master. It would be useful (this would allow per-standby buffer tables, for that matter), but it would also be tremendously difficult. Seems worthy of a TODO, though. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] crash-safe visibility map, take three
On 2010-11-30 05:57, Robert Haas wrote: Last week, I posted a couple of possible designs for making the visibility map crash-safe, which did not elicit much comment. Since this is an important prerequisite to index-only scans, I'm trying again. The logic seems to be: * If the visibillity map should be crash-safe if should be WAL-logged. * PD_ALL_VISIBLE is currently not being WAL-logged when vacuum sets it. * WAL logging the visibillity map bit is not "that" bad (small size). * WAL-logging the PD_ALL_VISIBLE bit would can WAL-record for the entire relation to be written out (potentially huge). Would the problem not be solved by not "trying to keep the two bits in sync" but simply removing the PD_ALL_VISIBLE bit in the page-header in favor for the bit in the visibillity map, that is now WAL-logged and thus safe to trust? Then vacuum could emit WAL records for setting the visibillity map bits, combined with changes on the page could clear it? The question probably boils down to: Given a crash-safe visibility map, what purpuse does the PD_ALL_VISIBLE bit serve? I've probably just missed some logic? Having index-only scans per-table ajustable would make quite some sense.. I have a couple of tables with a high turn-over rate that never get out of the OS-cache anyway, the benefit of index-only scans are quite small, especially if they come with additional overhead on INSERT/UPDATE/DELETE operations, whereas I also have huge tables with a very small amount of changes. Just the saved IO of not having to go to the heap in some cases would be good. I could see some benefits in having the index-only scan work on tuple-level visibillity information and not page-level, but that would require a bigger map (allthough still less than 1% of the heap size if my calculations are correct), but would enable visibillity testing of a tuple without going to the heap even other (unrelated) changes happend on the same page. Jesper -- Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reduce spurious Hot Standby conflicts from never-visible records
On Wed, Jan 5, 2011 at 3:06 PM, Robert Haas wrote: > On Wed, Jan 5, 2011 at 3:00 PM, Simon Riggs wrote: >> On Mon, 2011-01-03 at 23:13 -0500, Robert Haas wrote: >> >>> > Hmmm, my earlier code took xmax only if xmax > xmin. That was wrong; >>> > what I have now is better, but your point is there may be an even better >>> > truth. I'll think on that a little more. >> >> I remember that I thought some more on this and decided that I couldn't >> see a problem. I also see I didn't update the list to say that. >> >>> I guess the problem case here is something like: >>> >>> 1. T1 begins. T1 writes a tuple A (so it gets an XID). >>> 2. T2 begins. T2 writes a tuple B (so it gets a later XID). >>> 3. T1 takes a new snapshot that can see B and deletes B. >>> 4. T2 commits. >>> 5. T1 commits. >> >> How is step (3) possible before step (4)? > > At read committed isolation level, which is the default, we take a new > snapshot after every command. Oh, I'm a dork. You're saying T2 hasn't committed yet. Let me think about this some more... -- 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] WIP: Range Types
On Wed, 2011-01-05 at 10:41 -0800, David Fetter wrote: > On Thu, Jan 06, 2011 at 02:25:01AM +0900, Hitoshi Harada wrote: > > For any type to calculate boundary based on RANGE clause in window > > functions, we need some type interface mechanism in the core to know > > how to add / subtract values to reach the boundary from the current > > value. For example, > > > > SELECT count(*) OVER (ORDER BY n_int RANGE BETWEEN 10 PRECEDING AND 5 > > FOLLOWING) FROM tbl; > > I'm not sure I get the connection between this type of range and the > "range types" Jeff is working on. Jeff's work involves a way to > create types which represent ranges over types which have some kind of > ordering, although not necessarily a successor operation. > > Had you planned to cast to an integer range in the process of doing > this window? I don't think Harada-san needs the type infrastructure itself, just the interface to find the "difference type" (e.g. "interval" from "timestamp") as well as functions like add and subtract (potentially two interesting subtract functions). Without knowing which function to call, there is no way to find the window boundaries given the current row. The current design for range types doesn't ask for add or subtract. Although it might be interesting to try to use such an interface for range types, it introduces a lot of complexity and makes it easier to cause subtle problems (consider that addition of timestamps and intervals is not commutative). Even if add and subtract were associated with a range type, there's no way to tell which range type to pick given the window function syntax (multiple range types could be defined over the same subtype). I think the interface question should be addressed more directly with a "type interfaces" patch. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reduce spurious Hot Standby conflicts from never-visible records
On Wed, Jan 5, 2011 at 3:00 PM, Simon Riggs wrote: > On Mon, 2011-01-03 at 23:13 -0500, Robert Haas wrote: > >> > Hmmm, my earlier code took xmax only if xmax > xmin. That was wrong; >> > what I have now is better, but your point is there may be an even better >> > truth. I'll think on that a little more. > > I remember that I thought some more on this and decided that I couldn't > see a problem. I also see I didn't update the list to say that. > >> I guess the problem case here is something like: >> >> 1. T1 begins. T1 writes a tuple A (so it gets an XID). >> 2. T2 begins. T2 writes a tuple B (so it gets a later XID). >> 3. T1 takes a new snapshot that can see B and deletes B. >> 4. T2 commits. >> 5. T1 commits. > > How is step (3) possible before step (4)? At read committed isolation level, which is the default, we take a new snapshot after every command. > There are later errors in your example also. Well, point them out and let's discuss. -- 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] Re: [COMMITTERS] pgsql: Reduce spurious Hot Standby conflicts from never-visible records
On Mon, 2011-01-03 at 23:13 -0500, Robert Haas wrote: > > Hmmm, my earlier code took xmax only if xmax > xmin. That was wrong; > > what I have now is better, but your point is there may be an even better > > truth. I'll think on that a little more. I remember that I thought some more on this and decided that I couldn't see a problem. I also see I didn't update the list to say that. > I guess the problem case here is something like: > > 1. T1 begins. T1 writes a tuple A (so it gets an XID). > 2. T2 begins. T2 writes a tuple B (so it gets a later XID). > 3. T1 takes a new snapshot that can see B and deletes B. > 4. T2 commits. > 5. T1 commits. How is step (3) possible before step (4)? There are later errors in your example also. It's possible that that's all wrong because of how my head is feeling, if so please explain long hand so I don't have to think too much. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] making an unlogged table logged
On Jan 5, 2011, at 2:37 PM, Robert Haas wrote: > On Wed, Jan 5, 2011 at 2:36 PM, Simon Riggs wrote: >> The lock strength selected on the master doesn't need to be the same as >> the lock strength on the standby. You could quite easily generate AEL >> lock records to send to standby, without actually taking that lock level >> on the master. > > True. > >> Question: what does an unlogged table look like on the standby? Are they >> visible at all, does it throw an error, or do they just look empty. We >> probably need some docs in the HS section to explain that. I hope the >> answer isn't "look empty" since that is effectively data loss for people >> spreading queries across multiple nodes. > > Error. Hm- if the unlogged tables are being used as HTTP transient state storage, it would be handy to have that (admittedly non-essential) data on the standby when it becomes master, even if there are no guarantees surrounding the data beyond "it looked like this at some point". Since the tables are not writing WAL, would it be possible to allow for writing to unlogged tables on the standby to allow for out-of-band syncing? Otherwise, it seems the only alternative is to push changes to a separate database on the standby machine and then suck the data in when it becomes master. Cheers, M -- 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] making an unlogged table logged
On Wed, Jan 5, 2011 at 2:36 PM, Simon Riggs wrote: > The lock strength selected on the master doesn't need to be the same as > the lock strength on the standby. You could quite easily generate AEL > lock records to send to standby, without actually taking that lock level > on the master. True. > Question: what does an unlogged table look like on the standby? Are they > visible at all, does it throw an error, or do they just look empty. We > probably need some docs in the HS section to explain that. I hope the > answer isn't "look empty" since that is effectively data loss for people > spreading queries across multiple nodes. Error. -- 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] Upgrading Extension, version numbers
Robert Haas writes: > My understanding of the consensus is that it wasn't felt necessary for > the purpose for which it was proposed. I think it could be > re-proposed with a different argument and very possibly accepted. Sure. I'd still prefer us to adopt the solution I've been promoting, obviously, which I think has more merits. Namely no directory scanning, easy to support extension names such as postgis-1.5, and easy to support for a single upgrade file supporting upgrades from more than a single version, and bypassing entirely the need to know what version numbering scheme is in use: you just don't need to know how to compute previous or next version number. Now it's all about tradeoffs, and I'm just trying to explain what the one I'm doing here seems to me to have lot of sense. 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] making an unlogged table logged
On Tue, 2011-01-04 at 21:41 -0500, Robert Haas wrote: > 6. If wal_level != minimal, XLOG every page of every fork except the > init fork, for both the table and the associated indexes. (Note that > this step also requires an AccessExclusiveLock rather than some weaker > lock, because of the arbitrary rule that only AccessExclusiveLocks are > sent to standbys. If we held only ShareRowExclusiveLock on the > master, for example, a Hot Standby backend might read the table while > it's only been half-copied.) That rule is not arbitrary, there is simply no need to send other lock types since they would not conflict with queries. It's an optimisation. The lock strength selected on the master doesn't need to be the same as the lock strength on the standby. You could quite easily generate AEL lock records to send to standby, without actually taking that lock level on the master. Question: what does an unlogged table look like on the standby? Are they visible at all, does it throw an error, or do they just look empty. We probably need some docs in the HS section to explain that. I hope the answer isn't "look empty" since that is effectively data loss for people spreading queries across multiple nodes. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] We need to log aborted autovacuums
> If you could gather more info on whether this logging catches the > problem cases you're seeing, that would really be the right test for the > patch's usefulness. I'd give you solid 50/50 odds that you've correctly > diagnosed the issue, and knowing for sure would make advocating for this > logging a pretty easy sell to me at least. Well, I already resolved the issue through polling pg_locks. The issue was IIT sessions which lasted up to an hour, which we fixed in the application, so I don't have the test case available anymore. I'd have to generate a synthetic test case, and since I agree that a SQL-callable diagnostic is superior to logging, I don't think we should pursue the log levels further. > Or should it perhaps be a per-table counter in pg_stat_user_tables, > given your statement above? Or even a timestamp: last_autovacuum_attempt, which would record the last time autovacuum was tried. If that's fairly recent and you have a large number of dead rows, you know what kind of problem you have and can turn on debug. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] ALTER EXTENSION UPGRADE patch v1
David Fetter writes: > The syntax by itself does nothing, but the underlying capability gives > users: Ok, now I understand that the syntax you proposed was a shortcut for an I-want-it-all request :) > - The ability to have versions of software on different databases on > the same system. > > - The ability to do deterministic upgrades, rather than just, "upgrade > me to the latest, which may be buggy and/or slow things down to > avoid a problem I know I don't have." Both depends on a filesystem organization rework. > I am not saying that this is a show-stopper. I *am* saying that > multiple concurrent versions and deterministic upgrades are common > enough requests that you shouldn't do things that would prevent those > in the future. Would it be useful to have the syntax support in 9.1 already, but which would only check that the asked-of new version (and current version) are the one currently available (and installed), and ERROR out otherwise? I think that syntax-and-check is still doable for 9.1, if there's a will to get there. 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] making an unlogged table logged
On Wed, Jan 5, 2011 at 2:02 PM, Josh Berkus wrote: > Note that the conversion both ways can be worked around with the > CREATE/ALTER TABLE dosiedo, so I don't think either of these is critical > for 9.1. Unless you feel like working on them, of course. As I said in my OP, I don't intend to work on them for 9.1, but will review and possibly commit a patch written by someone else. It'd be nice to have, but there are other things I want more, and a ton of other large and small patches to review. -- 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] making an unlogged table logged
On 1/4/11 6:41 PM, Robert Haas wrote: > Going the other direction ought to be possible too, although it seems > somewhat less useful. Actually, it's more useful; many people who *upgrade* to 9.1 will wand to convert one or two of their tables to unlogged. Note that the conversion both ways can be worked around with the CREATE/ALTER TABLE dosiedo, so I don't think either of these is critical for 9.1. Unless you feel like working on them, of course. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] WIP: Range Types
On Thu, Jan 06, 2011 at 02:25:01AM +0900, Hitoshi Harada wrote: > 2011/1/5 Jeff Davis : > > On Tue, 2011-01-04 at 23:04 +0900, Hitoshi Harada wrote: > >> > CREATE TYPE numrange > >> > AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp); > >> > >> I am interested in how you define increment/decrement operation > >> of range value in discrete types. The window functions and > >> PARTITION also want to represent RANGE but there's no clear > >> solution. > > > > The user would specify a "canonical" function like: > > > > CREATE TYPE int4range AS RANGE (SUBTYPE=int4, SUBTYPE_CMP=btint4cmp, > > CANONICAL=my_int4range_canonical); > > > > That function would be called when constructing ranges on input or after > > a computation, and could change something like (1,4] into [2,4] if you > > prefer the latter form. > > > > So the range types would not have increments, decrements, granules, or > > knowledge about the "difference" type (e.g. "interval" is the difference > > type for timestamp). > > To canonicalize, it might be ok. I wonder if you won't operate on > the range types like extending their bounds or iterate/enum values > from start to end. In such situation, I bet you'll need to know how > to walk values step by step. > > > What support do you need/want from range types to help with new window > > function features? > > > My argument is here: > http://archives.postgresql.org/message-id/aanlktimfmqmbzj5ctxve_pwt_zmcuhpoet3gaqq6p...@mail.gmail.com > > For any type to calculate boundary based on RANGE clause in window > functions, we need some type interface mechanism in the core to know > how to add / subtract values to reach the boundary from the current > value. For example, > > SELECT count(*) OVER (ORDER BY n_int RANGE BETWEEN 10 PRECEDING AND 5 > FOLLOWING) FROM tbl; I'm not sure I get the connection between this type of range and the "range types" Jeff is working on. Jeff's work involves a way to create types which represent ranges over types which have some kind of ordering, although not necessarily a successor operation. Had you planned to cast to an integer range in the process of doing this window? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Upgrading Extension, version numbers
On Jan 5, 2011, at 10:05 AM, Robert Haas wrote: >> There's no consensus to publish a bakend \i like function. So there's >> no support for this upgrade script organizing you're promoting. Unless >> the consensus changes again (but a commit has been done). > > My understanding of the consensus is that it wasn't felt necessary for > the purpose for which it was proposed. I think it could be > re-proposed with a different argument and very possibly accepted. +1 Yes, exactly. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visual Studio 2010/Windows SDK 7.1 support
On Wed, Jan 5, 2011 at 12:59 PM, Andrew Dunstan wrote: > I've abandoned the use of an external diff program for git. If I need > context diffs I produce them by piping normal git diff output through > "filterdiff --format=context". One possible disadvantage of that it strips out this stuff: diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 6a9b21d..1c6ae02 100644 I actually haven't figured out exactly under what circumstances that index information is used by git, but I think in theory it's supposed to make it possible for git to apply patches that would otherwise fail. However, I actually haven't figured out the best way to make git do that, either; I'm still using patch -p1, which is basically horrible if there are >0 conflicts. -- 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] Upgrading Extension, version numbers
On Tue, Jan 4, 2011 at 2:48 PM, Dimitri Fontaine wrote: > "David E. Wheeler" writes: >> As Tom pointed out, you can do the same with naming conventions by having >> scripts \i each other as appropriate. > > This is a deprecated idea, though. We're talking about the > pg_execute_from_file() patch that has been applied, but without the > pg_execute_sql_file() function. So that part is internal to the backend > extension code and not available from SQL anymore. > > There's no consensus to publish a bakend \i like function. So there's > no support for this upgrade script organizing you're promoting. Unless > the consensus changes again (but a commit has been done). My understanding of the consensus is that it wasn't felt necessary for the purpose for which it was proposed. I think it could be re-proposed with a different argument and very possibly accepted. -- 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] Visual Studio 2010/Windows SDK 7.1 support
On 01/05/2011 12:33 PM, Magnus Hagander wrote: It's actually the same patch as before with the corrupted line-endings parsed out. "git diff master VS2010> ../VS2010.patch" reproduceabyl generates those on my (windows) system when I use git-external-diff configured as described in http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git to produce context diffs. Yeah, I'm not sure those instructions really take Windows into account :-) I've abandoned the use of an external diff program for git. If I need context diffs I produce them by piping normal git diff output through "filterdiff --format=context". 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] Support for negative index values in array fetching
2011/1/5 Florian Pflug : > On Jan5, 2011, at 15:17 , Pavel Stehule wrote: >> 2011/1/5 Florian Pflug : >>> How so? You'd still be able to get the last element by simply writing >>> >>> array_relative(some_array, array[-1]). >>> >>> Or, if we made the function variadic, by writing >>> >>> array_relative(some_array, -1). >> >> Sorry, but It isn't too intuitive. Minimally for me. Why you don't >> thinking about simple functions with only positive arguments. There >> are only four combinations. I don't think we must have only one super >> function. >> >> we need functionality for: >> >> a) get first n items >> b) get items without last n items >> c) get last n items >> d) skip first n items > > Now you've moved the goalpost - the OP wanted to access individual > elements, not slices! To support slices, a three-argument version > of array_relative() would be required, with the signature > I am not sure. Usually need both when I play with a stack I need a) FIFO - first element from array and all others without first element b) LIFO - last element from array and all others without last element The game with queues is only one use case that I know where I need access to relative indexed items in array. Maybe is other, but I don't know it. ??? I don't know why I need a access to relative indexed items? Pavel > array_relative(some_array anyarray, first int[], last int[]) > > Your requirements (a) to (d) are then easily satisfied > > a) array_relative(ary, array[0], array[n-1]) > b) array_relative(ary, array[0], array[-n-1]) > c) array_relative(ary, array[-n], array[-1]) > d) array_relative(ary, array[n], array[-1]) > what is n?? it's not implementable. > The individual function approach might be a tad more readable for > one-dimensional arrays, but they don't scale well to the general > case. > > Maybe the OP could comment on whether any of these solutions > would fit his needs? > > best regards, > Florian Pflug > > -- 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] Visual Studio 2010/Windows SDK 7.1 support
On Mon, Jan 3, 2011 at 16:45, Brar Piening wrote: > On Mon, 3 Jan 2011 10:44:19 +0100, Magnus Hagander > wrote: >> >> Yeah, it looks that way - it's missing the ordering of the contrib >> I'll run it once for that now, and then please rebase your >> patch on top of that - makes it easier to review it. > > The rebased patch can be grabbed from http://www.piening.info/VS2010v2.patch Thanks. It still has windows linebreaks, but only once now, so they can be properly removed by patch. It applies cleanly now. > It's actually the same patch as before with the corrupted line-endings > parsed out. > > "git diff master VS2010 > ../VS2010.patch" reproduceabyl generates those on > my (windows) system when I use git-external-diff configured as described in > http://wiki.postgresql.org/wiki/Working_with_Git#Context_diffs_with_Git to > produce context diffs. Yeah, I'm not sure those instructions really take Windows into account :-) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] WIP: Range Types
2011/1/5 Jeff Davis : > On Tue, 2011-01-04 at 23:04 +0900, Hitoshi Harada wrote: >> > CREATE TYPE numrange >> > AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp); >> >> I am interested in how you define increment/decrement operation of >> range value in discrete types. The window functions and PARTITION also >> want to represent RANGE but there's no clear solution. > > The user would specify a "canonical" function like: > > CREATE TYPE int4range AS RANGE (SUBTYPE=int4, SUBTYPE_CMP=btint4cmp, > CANONICAL=my_int4range_canonical); > > That function would be called when constructing ranges on input or after > a computation, and could change something like (1,4] into [2,4] if you > prefer the latter form. > > So the range types would not have increments, decrements, granules, or > knowledge about the "difference" type (e.g. "interval" is the difference > type for timestamp). To canonicalize, it might be ok. I wonder if you won't operate on the range types like extending their bounds or iterate/enum values from start to end. In such situation, I bet you'll need to know how to walk values step by step. > What support do you need/want from range types to help with new window > function features? > My argument is here: http://archives.postgresql.org/message-id/aanlktimfmqmbzj5ctxve_pwt_zmcuhpoet3gaqq6p...@mail.gmail.com For any type to calculate boundary based on RANGE clause in window functions, we need some type interface mechanism in the core to know how to add / subtract values to reach the boundary from the current value. For example, SELECT count(*) OVER (ORDER BY n_int RANGE BETWEEN 10 PRECEDING AND 5 FOLLOWING) FROM tbl; In the standard, the types allowed in RANGE are only int, float, date, timestamp, etc. but we have various extensible data types as you know and we couldn't assume '+' / '-' operators tell add /subtract operation absolutely. > Also, partitioning might have some use for range types to represent > range partitions. Comments are welcome. I heard about partitioning which may have the same problem in RANGE clause from Itagaki-san, but have not looked so much yet. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Range Types
2011/1/5 Jeff Davis : > On Tue, 2011-01-04 at 23:04 +0900, Hitoshi Harada wrote: >> > CREATE TYPE numrange >> > AS RANGE (SUBTYPE=numeric, SUBTYPE_CMP=numeric_cmp); >> >> I am interested in how you define increment/decrement operation of >> range value in discrete types. The window functions and PARTITION also >> want to represent RANGE but there's no clear solution. > > The user would specify a "canonical" function like: > > CREATE TYPE int4range AS RANGE (SUBTYPE=int4, SUBTYPE_CMP=btint4cmp, > CANONICAL=my_int4range_canonical); > > That function would be called when constructing ranges on input or after > a computation, and could change something like (1,4] into [2,4] if you > prefer the latter form. > > So the range types would not have increments, decrements, granules, or > knowledge about the "difference" type (e.g. "interval" is the difference > type for timestamp). To canonicalize, it might be ok. I wonder if you won't operate on the range types like extending their bounds or iterate/enum values from start to end. In such situation, I bet you'll need to know how to walk values step by step. > What support do you need/want from range types to help with new window > function features? > My argument is here: http://archives.postgresql.org/message-id/aanlktimfmqmbzj5ctxve_pwt_zmcuhpoet3gaqq6p...@mail.gmail.com For any type to calculate boundary based on RANGE clause in window functions, we need some type interface mechanism in the core to know how to add / subtract values to reach the boundary from the current value. For example, SELECT count(*) OVER (ORDER BY n_int RANGE BETWEEN 10 PRECEDING AND 5 FOLLOWING) FROM tbl; In the standard, the types allowed in RANGE are only int, float, date, timestamp, etc. but we have various extensible data types as you know and we couldn't assume '+' / '-' operators tell add /subtract operation absolutely. > Also, partitioning might have some use for range types to represent > range partitions. Comments are welcome. I heard about partitioning which may have the same problem in RANGE clause from Itagaki-san, but have not looked so much yet. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] What is lo_insert?
The psql manual page mentions lo_insert: LASTOID The value of the last affected OID, as returned -->from an INSERT or lo_insert command. This variable is only guaranteed to be valid until after the result of the next SQL command has been displayed. Should that be lo_import? I don't see lo_insert used anywhere. And it doesn't seem lo_import sets LASTOID. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade cleanup for map struct creation
The attached, applied patch clarifies pg_upgrade's creation of the map
file structure. It also cleans up pg_dump's calling of
pg_upgrade_support functions.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c
index 8d566c0..83afb92 100644
*** /tmp/pgdiff.7809/Jqhj6c_info.c Wed Jan 5 11:35:06 2011
--- contrib/pg_upgrade/info.c Wed Jan 5 11:29:51 2011
*** static RelInfo *relarr_lookup_rel_oid(Cl
*** 33,40
* generates database mappings for "old_db" and "new_db". Returns a malloc'ed
* array of mappings. nmaps is a return parameter which refers to the number
* mappings.
- *
- * NOTE: Its the Caller's responsibility to free the returned array.
*/
FileNameMap *
gen_db_file_maps(DbInfo *old_db, DbInfo *new_db,
--- 33,38
*** gen_db_file_maps(DbInfo *old_db, DbInfo
*** 45,63
int num_maps = 0;
maps = (FileNameMap *) pg_malloc(sizeof(FileNameMap) *
! new_db->rel_arr.nrels);
! for (relnum = 0; relnum < new_db->rel_arr.nrels; relnum++)
{
! RelInfo*newrel = &new_db->rel_arr.rels[relnum];
! RelInfo*oldrel;
! /* toast tables are handled by their parent */
! if (strcmp(newrel->nspname, "pg_toast") == 0)
continue;
! oldrel = relarr_lookup_rel_name(&old_cluster, &old_db->rel_arr,
! newrel->nspname, newrel->relname);
create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db,
oldrel, newrel, maps + num_maps);
--- 43,61
int num_maps = 0;
maps = (FileNameMap *) pg_malloc(sizeof(FileNameMap) *
! old_db->rel_arr.nrels);
! for (relnum = 0; relnum < old_db->rel_arr.nrels; relnum++)
{
! RelInfo*oldrel = &old_db->rel_arr.rels[relnum];
! RelInfo*newrel;
! /* toast tables are handled by their parents */
! if (strcmp(oldrel->nspname, "pg_toast") == 0)
continue;
! newrel = relarr_lookup_rel_name(&old_cluster, &old_db->rel_arr,
! oldrel->nspname, oldrel->relname);
create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db,
oldrel, newrel, maps + num_maps);
*** gen_db_file_maps(DbInfo *old_db, DbInfo
*** 65,116
/*
* So much for mapping this relation; now we need a mapping
! * for its corresponding toast relation, if any.
*/
if (oldrel->toastrelid > 0)
{
! RelInfo*new_toast;
! RelInfo*old_toast;
! char new_name[MAXPGPATH];
! char old_name[MAXPGPATH];
!
! /* construct the new and old relnames for the toast relation */
! snprintf(old_name, sizeof(old_name), "pg_toast_%u", oldrel->reloid);
! snprintf(new_name, sizeof(new_name), "pg_toast_%u", newrel->reloid);
- /* look them up in their respective arrays */
old_toast = relarr_lookup_rel_oid(&old_cluster, &old_db->rel_arr,
! oldrel->toastrelid);
! new_toast = relarr_lookup_rel_name(&new_cluster, &new_db->rel_arr,
! "pg_toast", new_name);
- /* finally create a mapping for them */
create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db,
old_toast, new_toast, maps + num_maps);
num_maps++;
/*
! * also need to provide a mapping for the index of this toast
* relation. The procedure is similar to what we did above for
* toast relation itself, the only difference being that the
* relnames need to be appended with _index.
*/
-
- /*
- * construct the new and old relnames for the toast index
- * relations
- */
snprintf(old_name, sizeof(old_name), "%s_index", old_toast->relname);
! snprintf(new_name, sizeof(new_name), "pg_toast_%u_index",
! newrel->reloid);
- /* look them up in their respective arrays */
old_toast = relarr_lookup_rel_name(&old_cluster, &old_db->rel_arr,
"pg_toast", old_name);
new_toast = relarr_lookup_rel_name(&new_cluster, &new_db->rel_arr,
"pg_toast", new_name);
- /* finally create a mapping for them */
create_rel_filename_map(old_pgdata, new_pgdata, old_db,
new_db, old_toast, new_toast, maps + num_maps);
num_maps++;
--- 63,98
/*
* So much for mapping this relation; now we need a mapping
! * for its corresponding toast relation and toast index, if any.
*/
if (oldrel->toastrelid > 0)
{
! char old_name[MAXPGPATH], new_name[MAXPGPATH];
! RelInfo*old_toast, *new_toast;
old_toast = relarr_lookup_rel_oid(&old_cluster, &old_db->rel_arr,
! oldrel->toastrelid);
! new_toast = relarr_lookup_rel_oid(&new_cluster, &new_db->rel_arr,
! newrel->toastrelid);
create_rel_filename_map(old_pgdata, new_pgdata, old_db, new_db,
old_toast, new_toast, maps + num_maps);
num_maps++;
/*
! * We also need to provide a mapping
Re: [HACKERS] Support for negative index values in array fetching
On Jan5, 2011, at 15:17 , Pavel Stehule wrote: > 2011/1/5 Florian Pflug : >> How so? You'd still be able to get the last element by simply writing >> >> array_relative(some_array, array[-1]). >> >> Or, if we made the function variadic, by writing >> >> array_relative(some_array, -1). > > Sorry, but It isn't too intuitive. Minimally for me. Why you don't > thinking about simple functions with only positive arguments. There > are only four combinations. I don't think we must have only one super > function. > > we need functionality for: > > a) get first n items > b) get items without last n items > c) get last n items > d) skip first n items Now you've moved the goalpost - the OP wanted to access individual elements, not slices! To support slices, a three-argument version of array_relative() would be required, with the signature array_relative(some_array anyarray, first int[], last int[]) Your requirements (a) to (d) are then easily satisfied a) array_relative(ary, array[0], array[n-1]) b) array_relative(ary, array[0], array[-n-1]) c) array_relative(ary, array[-n], array[-1]) d) array_relative(ary, array[n], array[-1]) The individual function approach might be a tad more readable for one-dimensional arrays, but they don't scale well to the general case. Maybe the OP could comment on whether any of these solutions would fit his needs? best regards, Florian Pflug -- 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_upgrade patches applied
Tom Lane wrote:
> Bruce Momjian writes:
> > Tom Lane wrote:
> >> That isn't going to work. At least not unless you start trying to force
> >> roles to have the same OIDs in the new installation.
>
> > If so I can use the CREATE ROLE ... SYSID clause when doing a binary
> > upgrade.
>
> Oh, I had forgotten we still had that wart in the grammar.
> It doesn't actually work:
>
> else if (strcmp(defel->defname, "sysid") == 0)
> {
> ereport(NOTICE,
> (errmsg("SYSID can no longer be
> specified")));
> }
>
> Not sure if it's better to try to make that work again than to add
> another hack in pg_upgrade_support. On the whole that's a keyword
> I'd rather see us drop someday soon.
OK, let me work on adding it to pg_upgrade_support. Glad you saw this.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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_upgrade patches applied
Bruce Momjian writes:
> Tom Lane wrote:
>> That isn't going to work. At least not unless you start trying to force
>> roles to have the same OIDs in the new installation.
> If so I can use the CREATE ROLE ... SYSID clause when doing a binary
> upgrade.
Oh, I had forgotten we still had that wart in the grammar.
It doesn't actually work:
else if (strcmp(defel->defname, "sysid") == 0)
{
ereport(NOTICE,
(errmsg("SYSID can no longer be
specified")));
}
Not sure if it's better to try to make that work again than to add
another hack in pg_upgrade_support. On the whole that's a keyword
I'd rather see us drop someday soon.
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] ALTER EXTENSION UPGRADE patch v1
On Wed, Jan 05, 2011 at 10:19:23AM +0100, Dimitri Fontaine wrote: > David Fetter writes: > > One could imagine that an extension was updated more quickly than > > PostgreSQL major versions come out, or at least not at the exact same > > time. > > Sure, but I don't see what your proposed syntax is giving us here. The syntax by itself does nothing, but the underlying capability gives users: - The ability to have versions of software on different databases on the same system. - The ability to do deterministic upgrades, rather than just, "upgrade me to the latest, which may be buggy and/or slow things down to avoid a problem I know I don't have." > Currently the new version you're upgraded to is the one installed on > the file system, and whose .so has already been upgraded, and that's > it. > > I still need a better grasp at what your proposal is about to > comment and integrate, I fear. I am not saying that this is a show-stopper. I *am* saying that multiple concurrent versions and deterministic upgrades are common enough requests that you shouldn't do things that would prevent those in the future. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade patches applied
Tom Lane wrote: > Bruce Momjian writes: > > The last patch fixes a problem where I was not migrating > > pg_largeobject_metadata and its index for 9.0+ migrations, which of > > course would only affect migrations to 9.1 and 9.0 to 9.0 migrations, so > > I backpatched that to 9.0. > > That isn't going to work. At least not unless you start trying to force > roles to have the same OIDs in the new installation. Uh, don't we store the pg_shadow.usesysid in pg_largeobject_metadata? If so I can use the CREATE ROLE ... SYSID clause when doing a binary upgrade. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Streaming base backups
On 01/05/2011 02:54 PM, Magnus Hagander wrote: [..] Some remaining thoughts and must-dos: * Compression: Do we want to be able to compress the backups server-side? Or defer that to whenever we get compression in libpq? (you can still tunnel it through for example SSH to get compression if you want to) My thinking is defer it. * Compression: We could still implement compression of the tar files in pg_streamrecv (probably easier, possibly more useful?) hmm compression would be nice but I don't think it is required for this initial implementation. * Windows support (need to implement readlink) * Tar code is copied from pg_dump and modified. Should we try to factor it out into port/? There are changes in the middle of it so it can't be done with the current calling points, it would need a refactor. I think it's not worth it, given how simple it is. Improvements I want to add, but that aren't required for basic operation: * Stefan mentiond it might be useful to put some posix_fadvise(POSIX_FADV_DONTNEED) in the process that streams all the files out. Seems useful, as long as that doesn't kick them out of the cache *completely*, for other backends as well. Do we know if that is the case? well my main concern is that a basebackup done that way might blew up the buffercache of the OS causing temporary performance issues. This might be more serious with an in-core solution than with what people use now because a number of backup software and tools (like some of the commercial backup solutions) employ various tricks to avoid that. One interesting tidbit i found was: http://insights.oetiker.ch/linux/fadvise/ which is very Linux specific but interesting nevertheless... Stefan -- 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] making an unlogged table logged
On Wed, Jan 5, 2011 at 7:48 AM, David Fetter wrote: > On Wed, Jan 05, 2011 at 09:04:08AM -0500, Robert Haas wrote: >> On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wrote: >> > 1. Could the making a table logged be a non-exclusive lock if the >> > ALTER is allowed to take a full checkpoint? >> >> No, that doesn't solve either of the two problems I described, >> unfortunately. That is too bad. >> >> > 2. Unlogged to logged has giant use case. >> >> Agree. >> >> > 3. In MySQL I have had to ALTER tables to engine BLACKHOLE because >> > they held data that was not vital, but the server was out of IO. >> > Going logged -> unlogged has a significant placed, I think. >> >> Interesting. So you'd change a logged table into an unlogged table >> to cut down on I/O, and take the risk of losing the data if the >> server went down? > > BLACKHOLE is a "storage engine" that's equivalent to /dev/null, so it > wasn't a risk /per se/. > Exactly. It was data I could live without and by having schema attached to /dev/null the application did not error out and die. It is a very bad option and being able to turn off logging for a table is a much better one. -- Rob Wultsch wult...@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] making an unlogged table logged
On Wed, Jan 05, 2011 at 09:04:08AM -0500, Robert Haas wrote: > On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wrote: > > 1. Could the making a table logged be a non-exclusive lock if the > > ALTER is allowed to take a full checkpoint? > > No, that doesn't solve either of the two problems I described, > unfortunately. > > > 2. Unlogged to logged has giant use case. > > Agree. > > > 3. In MySQL I have had to ALTER tables to engine BLACKHOLE because > > they held data that was not vital, but the server was out of IO. > > Going logged -> unlogged has a significant placed, I think. > > Interesting. So you'd change a logged table into an unlogged table > to cut down on I/O, and take the risk of losing the data if the > server went down? BLACKHOLE is a "storage engine" that's equivalent to /dev/null, so it wasn't a risk /per se/. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade patches applied
Bruce Momjian writes: > The last patch fixes a problem where I was not migrating > pg_largeobject_metadata and its index for 9.0+ migrations, which of > course would only affect migrations to 9.1 and 9.0 to 9.0 migrations, so > I backpatched that to 9.0. That isn't going to work. At least not unless you start trying to force roles to have the same OIDs in the new installation. 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] Support for negative index values in array fetching
2011/1/5 Florian Pflug : > On Jan5, 2011, at 13:08 , Pavel Stehule wrote: >> 2011/1/5 Florian Pflug : >>> On Jan5, 2011, at 10:25 , Peter Eisentraut wrote: On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote: > The only way around that would be to introduce magic constants "lower", > "upper" that > can be used within index expressions and evaluate to the indexed > dimension's lower > and upper bound. You'd then use > > my_array[upper], my_array[upper-1], ... > > to refer to the last, second-to-last, ... element in the array. Actually > doing this > could get pretty messy, though - not sure if it's really worth the > effort... How about just some functions: array_first(array, dim) array_last(array, dim) >>> >>> >>> You image these to return the actual element, not the first and last index >>> value, right? >>> Because we already have array_lower() and array_upper() which return the >>> lower and upper >>> index bound for a certain dimension. >>> (http://www.postgresql.org/docs/9.0/interactive/functions-array.htm) >>> >>> A more general solution would be a function >>> >>> array_relative(array anyarray, indices int[]) >>> >> >> I don't think so this design helps. instead maintaining a data array, >> you should to maintain a indices array. > > > How so? You'd still be able to get the last element by simply writing > > array_relative(some_array, array[-1]). > > Or, if we made the function variadic, by writing > > array_relative(some_array, -1). Sorry, but It isn't too intuitive. Minimally for me. Why you don't thinking about simple functions with only positive arguments. There are only four combinations. I don't think we must have only one super function. we need functionality for: a) get first n items b) get items without last n items c) get last n items d) skip first n items I think so this functionality is relative important, so we can use a richer api. Maybe we thinking about different use cases. Pavel > > It's essentially what the OP proposed, but with the function array_relative() > in place of > the indexing operator []. > > best regards, > Florian Pflug > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Range Types
On Wed, Jan 5, 2011 at 12:54 AM, Jeff Davis wrote: > On Tue, 2011-01-04 at 16:45 -0800, Josh Berkus wrote: >> On 1/4/11 10:18 AM, Jeff Davis wrote: >> > The main drawback here is that only a select group of people will be >> > defining discrete range types at all, because it would require them to >> > define a function first. Perhaps that's for the best, because, (as Tom >> > pointed out) we don't want someone using floats and then specifying a >> > granule of '0.01'. >> >> Frankly, I'm still not convinced that *anyone* will really need discrete >> range types > > Well, *need* is a standard that can never be met. But with something > like a date range, it's very possible that a discrete version matches > the real-world problem more closely than a continuous one. > > If you use only continuous ranges, then be careful to stick with exactly > one convention, or you will likely get wrong results (I think this point > has already been established). That sounds easy, but consider: > * If you want to know whether two ranges are adjacent (a common > requirement), then you need to use "[ )" or "( ]". > * If you need to map a single point into a range, the only thing that > makes sense is "[ ]". > * If your query contains current_date, you'll probably want ranges that > are either in "( ]" or "[ ]" form. > * If you are mixing data sets, they may use different conventions. > > You can work around all of these problems by making the query more > complex (and more error-prone). But I wouldn't like to give up on > discrete ranges for types where it really makes sense (dates, IPs, > integers). +1. -- 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] Streaming replication as a separate permissions
On Wed, Jan 5, 2011 at 8:24 AM, Magnus Hagander wrote: > Ok, done and applied. Thanks. -- 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] making an unlogged table logged
On Tue, Jan 4, 2011 at 10:56 PM, Rob Wultsch wrote: > 1. Could the making a table logged be a non-exclusive lock if the > ALTER is allowed to take a full checkpoint? No, that doesn't solve either of the two problems I described, unfortunately. > 2. Unlogged to logged has giant use case. Agree. > 3. In MySQL I have had to ALTER tables to engine BLACKHOLE because > they held data that was not vital, but the server was out of IO. Going > logged -> unlogged has a significant placed, I think. Interesting. So you'd change a logged table into an unlogged table to cut down on I/O, and take the risk of losing the data if the server went down? -- 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
[HACKERS] Streaming base backups
Attached is an updated streaming base backup patch, based off the work that Heikki started. It includes support for tablespaces, permissions, progress reporting and some actual documentation of the protocol changes (user interface documentation is going to be depending on exactly what the frontend client will look like, so I'm waiting with that one a while). The basic implementation is: Add a new command to the replication mode called BASE_BACKUP, that will initiate a base backup, stream the contents (in tar compatible format) of the data directory and all tablespaces, and then end the base backup in a single operation. Other than the basic implementation, there is a small refactoring done of pg_start_backup() and pg_stop_backup() splitting them into a "backend function" that is easier to call internally and a "user facing function" that remains identical to the previous one, and I've also added a pg_abort_backup() internal-only function to get out of crashes while in backup mode in a safer way (so it can be called from error handlers). Also, the walsender needs a resource owner in order to call pg_start_backup(). I've implemented a frontend for this in pg_streamrecv, based on the assumption that we wanted to include this in bin/ for 9.1 - and that it seems like a reasonable place to put it. This can obviously be moved elsewhere if we want to. That code needs a lot more cleanup, but I wanted to make sure I got the backend patch out for review quickly. You can find the current WIP branch for pg_streamrecv on my github page at https://github.com/mhagander/pg_streamrecv, in the branch "baserecv". I'll be posting that as a separate patch once it's been a bit more cleaned up (it does work now if you want to test it, though). Some remaining thoughts and must-dos: * Compression: Do we want to be able to compress the backups server-side? Or defer that to whenever we get compression in libpq? (you can still tunnel it through for example SSH to get compression if you want to) My thinking is defer it. * Compression: We could still implement compression of the tar files in pg_streamrecv (probably easier, possibly more useful?) * Windows support (need to implement readlink) * Tar code is copied from pg_dump and modified. Should we try to factor it out into port/? There are changes in the middle of it so it can't be done with the current calling points, it would need a refactor. I think it's not worth it, given how simple it is. Improvements I want to add, but that aren't required for basic operation: * Stefan mentiond it might be useful to put some posix_fadvise(POSIX_FADV_DONTNEED) in the process that streams all the files out. Seems useful, as long as that doesn't kick them out of the cache *completely*, for other backends as well. Do we know if that is the case? * include all the necessary WAL files in the backup. This way we could generate a tar file that would work on it's own - right now, you still need to set up log archiving (or use streaming repl) to get the remaining logfiles from the master. This is fine for replication setups, but not for backups. This would also require us to block recycling of WAL files during the backup, of course. * Suggestion from Heikki: don't put backup_label in $PGDATA during the backup. Rather, include it just in the tar file. That way if you crash during the backup, the master doesn't start recovery from the backup_label, leading to failure to start up in the worst case * Suggestion from Heikki: perhaps at some point we're going to need a full bison grammar for walsender commands. * Relocation of tablespaces (can at least partially be done client-side) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ *** a/doc/src/sgml/protocol.sgml --- b/doc/src/sgml/protocol.sgml *** *** 1458,1463 The commands accepted in walsender mode are: --- 1458,1560 + + + BASE_BACKUP options;label + + + Instructs the server to start streaming a base backup. + The system will automatically be put in backup mode with the label + specified in label before the backup is started, and + taken out of it when the backup is complete. The following options + are accepted: + + + PROGRESS + + + Request information required to generate a progress report. This will + send back an approximate size in the header of each tablespace, which + can be used to calculate how far along the stream is done. This is + calculated by enumerating all the file sizes once before the transfer + is even started, and may as such have a negative impact on the + performance - in particular it may take longer before the first data + is streamed. Since the database files can change during the backup, + the size is only approximate and may both grow
Re: [HACKERS] Support for negative index values in array fetching
On Jan5, 2011, at 13:08 , Pavel Stehule wrote: > 2011/1/5 Florian Pflug : >> On Jan5, 2011, at 10:25 , Peter Eisentraut wrote: >>> On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote: The only way around that would be to introduce magic constants "lower", "upper" that can be used within index expressions and evaluate to the indexed dimension's lower and upper bound. You'd then use my_array[upper], my_array[upper-1], ... to refer to the last, second-to-last, ... element in the array. Actually doing this could get pretty messy, though - not sure if it's really worth the effort... >>> >>> How about just some functions: >>> >>> array_first(array, dim) >>> array_last(array, dim) >> >> >> You image these to return the actual element, not the first and last index >> value, right? >> Because we already have array_lower() and array_upper() which return the >> lower and upper >> index bound for a certain dimension. >> (http://www.postgresql.org/docs/9.0/interactive/functions-array.htm) >> >> A more general solution would be a function >> >> array_relative(array anyarray, indices int[]) >> > > I don't think so this design helps. instead maintaining a data array, > you should to maintain a indices array. How so? You'd still be able to get the last element by simply writing array_relative(some_array, array[-1]). Or, if we made the function variadic, by writing array_relative(some_array, -1). It's essentially what the OP proposed, but with the function array_relative() in place of the indexing operator []. best regards, Florian Pflug -- 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] system views for walsender activity
On Wed, Jan 5, 2011 at 02:32, Itagaki Takahiro wrote: > On Wed, Jan 5, 2011 at 04:56, Heikki Linnakangas > wrote: >>> I think pg_stat_replication is better than pg_stat_standby, but I'm >>> still not convinced we shouldn't go with the obvious >>> pg_stat_walsenders. >> >> How about pg_stat_replication_activity? If I understood correctly, the view >> is similar to pg_stat_activity, but displays information about connected >> standbys rather than regular backends. It's a bit long name, though. > > The view currently discussed is for *master* servers. We might have some > views for replication activity in *standby* servers. So, I'd like to > choose consistent and symmetric names for them -- for example, > pg_stat_replication_master and pg_stat_replication_standby. > I've expected they will be pg_stat_wal_[senders|receivers] > when I was writing the patch, but any other better names welcome. > > However, we have "max_wal_senders" GUC parameter. So, users still > need to know what "wal_senders" is. An example to compare with could be pg_stat_bgwriter - that's also one the really expects you to know some internals. Now, it so happens that it's a very *bad* example, since it contains a bunch of information that's *not* actually about the bgwriter these days :-) But from that perspective, is it likely to ever contain anyting *other* than walsender information? Given that it's keyed by the process id of a walsender, I don't expect it would. Whereas a pg_stat_replication or such could equally be expected to contain information about other ways of replication - like the file based modes or even slony. +1 for pg_stat_walsender or pg_stat_walsender_activity -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Streaming replication as a separate permissions
On Wed, Jan 5, 2011 at 04:24, Robert Haas wrote: > On Mon, Jan 3, 2011 at 5:50 PM, Magnus Hagander wrote: >> On Mon, Jan 3, 2011 at 17:23, Robert Haas wrote: >>> On Mon, Jan 3, 2011 at 11:20 AM, Tom Lane wrote: Robert Haas writes: > On the other hand, the REPLICATION privilege is denying you the right to > perform an operation *even though you already are authenticated as a > superuser*. I don't think there's anywhere else in the system where > we allow a privilege to non-super-users but deny that same privilege > to super-users, and I don't think we should be starting now. You might want to reflect on rolcatupdate a bit before asserting that there are no cases where privileges are ever denied to superusers. >>> >>> Oh, huh. I wasn't aware of that. >>> However, that precedent would suggest that the default should be to grant the replication bit to superusers. >>> >>> Yes it would. >> >> Just to be clear: are we saying that "CREATE ROLE foo SUPERUSER" >> should grant both superuser and replication, as well as the default >> "postgres" user also having replication as well? > > I think that's what we're saying. Ok, done and applied. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] We need to log aborted autovacuums
On Wed, Jan 5, 2011 at 07:55, Greg Smith wrote: > a bit of work in userland, I don't see this even being justified as an INFO > or LOG level message. Anytime I can script a SQL-level monitor for > something that's easy to tie into Nagios or something, I greatly prefer that > to log file scraping for it anyway. + Log files can be very useful for details, but they suck for noticing the pattern in the first place :-) > verbosity of similar failure mode tests that follow it. Right now failure > to acquire a lock is just not considered a log-worthy issue, and I agree > that it's worth considering whether it should be. Or should it perhaps be a per-table counter in pg_stat_user_tables, given your statement above? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Support for negative index values in array fetching
2011/1/5 Florian Pflug : > On Jan5, 2011, at 10:25 , Peter Eisentraut wrote: >> On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote: >>> The only way around that would be to introduce magic constants "lower", >>> "upper" that >>> can be used within index expressions and evaluate to the indexed >>> dimension's lower >>> and upper bound. You'd then use >>> >>> my_array[upper], my_array[upper-1], ... >>> >>> to refer to the last, second-to-last, ... element in the array. Actually >>> doing this >>> could get pretty messy, though - not sure if it's really worth the effort... >> >> How about just some functions: >> >> array_first(array, dim) >> array_last(array, dim) > > > You image these to return the actual element, not the first and last index > value, right? > Because we already have array_lower() and array_upper() which return the > lower and upper > index bound for a certain dimension. > (http://www.postgresql.org/docs/9.0/interactive/functions-array.htm) > > A more general solution would be a function > > array_relative(array anyarray, indices int[]) > I don't think so this design helps. instead maintaining a data array, you should to maintain a indices array. Pavel > which would return the element indexed by , where positive indices > are assumed to > be relative to the respective dimension's lower bound and negative indices to > the > upper bound + 1. > > For slices, we could additionally have > > array_relative(array anyarray, indices_start int[], indices_end int[]) > > best regards, > Florian Pflug > > > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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] ALTER EXTENSION UPGRADE patch v1
David Fetter writes: > One could imagine that an extension was updated more quickly than > PostgreSQL major versions come out, or at least not at the exact same > time. Sure, but I don't see what your proposed syntax is giving us here. Currently the new version you're upgraded to is the one installed on the file system, and whose .so has already been upgraded, and that's it. I still need a better grasp at what your proposal is about to comment and integrate, I fear. 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] SQL/MED - core functionality
On Wed, Jan 5, 2011 at 19:24, Shigeru HANADA wrote: > 2) foreign_scan.patch includes ForeignScan executor node and > FdwRoutine interface I can see now Iterate() callback is called in per-tuple memory context. I'll adjust copy from API for the change. We don't need to export the executor state in CopyState. ForeignNext() still needs to materialize the slot. It seems reasonable for me to add tts_tableoid to TupleTableSlot and modify slot_getattr() to return the field for virtual and minimal tuples. Am I missing any problems here? Even if we still materialize tuples in 9.1, we would be better to use ExecStoreVirtualTuple() in file_fdw and pgsql_fdw for future optimization. -- Itagaki Takahiro -- 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] Support for negative index values in array fetching
On Jan5, 2011, at 10:25 , Peter Eisentraut wrote: > On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote: >> The only way around that would be to introduce magic constants "lower", >> "upper" that >> can be used within index expressions and evaluate to the indexed dimension's >> lower >> and upper bound. You'd then use >> >> my_array[upper], my_array[upper-1], ... >> >> to refer to the last, second-to-last, ... element in the array. Actually >> doing this >> could get pretty messy, though - not sure if it's really worth the effort... > > How about just some functions: > > array_first(array, dim) > array_last(array, dim) You image these to return the actual element, not the first and last index value, right? Because we already have array_lower() and array_upper() which return the lower and upper index bound for a certain dimension. (http://www.postgresql.org/docs/9.0/interactive/functions-array.htm) A more general solution would be a function array_relative(array anyarray, indices int[]) which would return the element indexed by , where positive indices are assumed to be relative to the respective dimension's lower bound and negative indices to the upper bound + 1. For slices, we could additionally have array_relative(array anyarray, indices_start int[], indices_end int[]) best regards, Florian Pflug -- 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] Support for negative index values in array fetching
Hello 2011/1/5 Peter Eisentraut : > On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote: >> > Here's a patch to add support for negative index values in fetching >> > elements from an array. >> negative arguments for array can be really strange >> That won't work. In SQL, array indices don't necessarily start with 0 (or 1, >> or *any* >> single value). > > FYI, this is true for PostgreSQL, but not in SQL in general. In the > standard, array indexes go from 1 to N. > >> The only way around that would be to introduce magic constants "lower", >> "upper" that >> can be used within index expressions and evaluate to the indexed dimension's >> lower >> and upper bound. You'd then use >> >> my_array[upper], my_array[upper-1], ... >> >> to refer to the last, second-to-last, ... element in the array. Actually >> doing this >> could get pretty messy, though - not sure if it's really worth the effort... > > How about just some functions: > > array_first(array, dim) > array_last(array, dim) > > Perhaps some variants for splice vs. scalar. Itakagi has a function trim_array in http://archives.postgresql.org/message-id/aanlktinrrubdsswvqo481sl0eygz830=mfkadk_kn...@mail.gmail.com patch. It's similar to array_first. I understand to a missing functionality for FIFO or LIFO queues implementation based on array. There can be function that reduce a array to first or last n items, and functions that returns first or last items. some like array_first(array, items), array_last(array, items), array_remove_first(array, items), array_remove_last(array, items) or some similar Pavel > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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] SQL/MED - core functionality
On Sat, 1 Jan 2011 23:54:05 -0500 Robert Haas wrote: > Hanada-san, can you rebase the fdw_scan patch over what I committed > and post an updated version ASAP? It'd be better for Heikki or Tom to > work on that part of this than me, since they have a better > understanding of the executor than I do, but I'm sure that they will > not want to work from the previously posted patches as the changes I > made are fairly extensive. I've rebased fdw_scan patch onto HEAD, and also split into two parts: 1) fdw_handler.patch includes HANDLER option syntax for CREATE/ALTER FOREIGN DATA WRAPPER 2) foreign_scan.patch includes ForeignScan executor node and FdwRoutine interface Regards, -- Shigeru Hanada 20110105-fdw_handler.patch.gz Description: Binary data 20110105-foreign_scan.patch.gz 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] Support for negative index values in array fetching
On sön, 2011-01-02 at 12:47 +0100, Florian Pflug wrote: > > Here's a patch to add support for negative index values in fetching > > elements from an array. > > That won't work. In SQL, array indices don't necessarily start with 0 (or 1, > or *any* > single value). FYI, this is true for PostgreSQL, but not in SQL in general. In the standard, array indexes go from 1 to N. > The only way around that would be to introduce magic constants "lower", > "upper" that > can be used within index expressions and evaluate to the indexed dimension's > lower > and upper bound. You'd then use > > my_array[upper], my_array[upper-1], ... > > to refer to the last, second-to-last, ... element in the array. Actually > doing this > could get pretty messy, though - not sure if it's really worth the effort... How about just some functions: array_first(array, dim) array_last(array, dim) Perhaps some variants for splice vs. scalar. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
