Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Sun, 2010-07-18 at 22:47 -0400, Robert Haas wrote: > But it seems > that it's far from clear what to do about it, and it's not the job of > this patch to fix it anyway. Agreed. > Regarding the actual patch, it looks mostly good. Questions: > > 1. Why in rewriteSupport.c are we adding a call to > heap_inplace_update() in some situations? Doesn't seem like this is > something we should need or want to be monkeying with. Hmm, yes, that looks like a hangover. Will change. No others similar. > 2. Instead of AlterTableGreatestLockLevel(), how about > AlterTableGetLockLevel()? Yeah, it's going to be the highest lock > level required by any subcommand, but it seems mildly overspecified. > I don't feel strongly about this one, though, if someone has a strong > contrary opinion... I felt it indicated the process it's using. Happy to change. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] crash-recovery replay of CREATE TABLESPACE is broken in HEAD
Bruce Momjian wrote:
> > The attached patch does as suggested. I added the recovery code to the
> > create tablespace function so I didn't have to duplicate all the code
> > that computes the path names.
> >
> > Attached.
>
> Uh, another question. Looking at the createdb recovery, I see:
>
> /*
> * Our theory for replaying a CREATE is to forcibly drop the target
> * subdirectory if present, then re-copy the source data. This may be
> * more work than needed, but it is simple to implement.
> */
> if (stat(dst_path, &st) == 0 && S_ISDIR(st.st_mode))
> {
> if (!rmtree(dst_path, true))
> ereport(WARNING,
> (errmsg("some useless files may be left behind in old
> database directory \"%s\"",
> dst_path)));
> }
>
> Should I be using rmtree() on the mkdir target?
>
> Also, the original tablespace recovery code did not drop the symlink
> first. I assume that was not a bug only because we don't support moving
> tablespaces:
For consistency with CREATE DATABASE recovery and for reliablity, I
coded the rmtree() call instead. Patch attached.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: src/backend/commands/tablespace.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.77
diff -c -c -r1.77 tablespace.c
*** src/backend/commands/tablespace.c 18 Jul 2010 04:47:46 - 1.77
--- src/backend/commands/tablespace.c 19 Jul 2010 04:59:03 -
***
*** 538,543
--- 538,544
char *linkloc = palloc(OIDCHARS + OIDCHARS + 1);
char *location_with_version_dir = palloc(strlen(location) + 1 +
strlen(TABLESPACE_VERSION_DIRECTORY) + 1);
+ struct stat st;
sprintf(linkloc, "pg_tblspc/%u", tablespaceoid);
sprintf(location_with_version_dir, "%s/%s", location,
***
*** 562,567
--- 563,584
location)));
}
+ if (InRecovery)
+ {
+ /*
+ * Our theory for replaying a CREATE is to forcibly drop the target
+ * subdirectory if present, and then recreate it. This may be
+ * more work than needed, but it is simple to implement.
+ */
+ if (stat(location_with_version_dir, &st) == 0 && S_ISDIR(st.st_mode))
+ {
+ if (!rmtree(location_with_version_dir, true))
+ ereport(WARNING,
+ (errmsg("some useless files may be left behind in old database directory \"%s\"",
+ location_with_version_dir)));
+ }
+ }
+
/*
* The creation of the version directory prevents more than one tablespace
* in a single location.
***
*** 580,585
--- 597,612
location_with_version_dir)));
}
+ /* Remove old symlink in recovery, in case it points to the wrong place */
+ if (InRecovery)
+ {
+ if (unlink(linkloc) < 0 && errno != ENOENT)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not remove symbolic link \"%s\": %m",
+ linkloc)));
+ }
+
/*
* Create the symlink under PGDATA
*/
--
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] Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Joe Conway wrote: > "make dcheck" is running now (although seems rather slow). Yeah, most of those tests completely reset the environment for each permutation. I thought about changing it to update back to the same "visible" initial state each time, but it struck me that since this would accumulate more and more dead tuples as the tests advanced, it would exercise different code paths, so I've kinda got it in mind to add the faster tests as *additional* tests rather than eliminating the existing ones. I know they're way to slow to consider including in the normal "make check" suite, but when (if?) we get a "test farm" set up, this sort of thing seems like it would be in scope. On the other hand, maybe we should have a "quick" set of dtester tests and a more comprehensive one? I'm open to ideas. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Sun, Jul 18, 2010 at 1:20 PM, Tom Lane wrote: > Andres Freund writes: >> On Sunday 18 July 2010 18:02:26 Simon Riggs wrote: >>> Then I think the fix is to look at the xmin values on all of the tables >>> used during planning and ensure that we only use constraint-based >>> optimisations in a serializable transaction when our top xmin is later >>> than the last DDL change (via its xmin). > >> Why not just use a the normal snapshot at that point? > > There isn't a "normal snapshot" that the planner should be relying on. > It doesn't know what snap the resulting plan will be used with. > > I'm unconvinced that this is a problem worth worrying about, but if it > is then Simon's probably got the right idea: check the xmin of a > pg_constraint row before depending on it for planning. Compare the > handling of indexes made with CREATE INDEX CONCURRENTLY. It generally seems like a Bad Thing to use one snapshot for planning and another snapshot for execution. For example, if one transaction (ostensibly serializable) runs a query twice in a row and in the mean time some other transaction redefines a function used by that query, the two runs will return different results, which is inconsistent with any serial order of execution of those transactions. But it seems that it's far from clear what to do about it, and it's not the job of this patch to fix it anyway. Regarding the actual patch, it looks mostly good. Questions: 1. Why in rewriteSupport.c are we adding a call to heap_inplace_update() in some situations? Doesn't seem like this is something we should need or want to be monkeying with. 2. Instead of AlterTableGreatestLockLevel(), how about AlterTableGetLockLevel()? Yeah, it's going to be the highest lock level required by any subcommand, but it seems mildly overspecified. I don't feel strongly about this one, though, if someone has a strong contrary opinion... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
On 07/18/2010 07:02 PM, Joe Conway wrote: > On 07/18/2010 11:41 AM, Kevin Grittner wrote: >> To run the tests included in the main patch (if you have python, >> twisted, etc., installed), after the make check, run make dcheck. > > Question about dcheck. After install of twisted, I get: > > 8<- > bash-4.1$ make dcheck > make -C src/test dcheck > make[1]: Entering directory `/opt/src/pgsql/src/test' > make -C regress dcheck > make[2]: Entering directory `/opt/src/pgsql/src/test/regress' > ./pg_dtester.py --temp-install --top-builddir=../../.. \ > --multibyte=SQL_ASCII > Traceback (most recent call last): > File "./pg_dtester.py", line 18, in > from dtester.events import EventMatcher, EventSource, Event, \ > ImportError: No module named dtester.events > 8<- > > Another python package I'm missing? Sorry for the noise -- I see the dependency listed on the wiki to Markus Wanner's dtester. Looks like "make dcheck" is running now (although seems rather slow). Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Patch: psql \whoami option
On Sun, 18 Jul 2010, David Christensen wrote:
It's helpful when you attach said patch. This has been rebased to current HEAD.
One minor thing I noticed in the updated patch.
You moved the '{' after the if(host) in command.c to it's own line(good) but
you used spaces instead of tabstops there, the same with the else.
Regards,
David
--
David Christensen
End Point Corporation
da...@endpoint.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] Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
On 07/18/2010 11:41 AM, Kevin Grittner wrote: > To run the tests included in the main patch (if you have python, > twisted, etc., installed), after the make check, run make dcheck. Question about dcheck. After install of twisted, I get: 8<- bash-4.1$ make dcheck make -C src/test dcheck make[1]: Entering directory `/opt/src/pgsql/src/test' make -C regress dcheck make[2]: Entering directory `/opt/src/pgsql/src/test/regress' ./pg_dtester.py --temp-install --top-builddir=../../.. \ --multibyte=SQL_ASCII Traceback (most recent call last): File "./pg_dtester.py", line 18, in from dtester.events import EventMatcher, EventSource, Event, \ ImportError: No module named dtester.events 8<- Another python package I'm missing? Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [HACKERS] SHOW TABLES
Kevin, * Kevin Grittner (kevin.gritt...@wicourts.gov) wrote: > I can't picture anything which could be done with views which would > allow me to issue one statement and see everything of interest about > a table (etc.). You know: tablespace, owner, permissions, columns, > primary key, foreign keys, check constraints, exclusion constraints, > ancestor tables, child tables, and whatever interesting features I > missed or we later add. You think that the users of the libpq() interface (or even the protocol itself) are going to handle getting \dt-type output back somehow..? As what, a single-column result of type text? And then they'll use non-fixed-width fonts, undoubtably, which means the results will end up looking rather ugly, even if we put in the effort to format the results. I'm becoming more and more inclined to just address this with newsysviews and encouraging use of the existing TABLE top-level command for people who have issue with 'SELECT *'. > Other products allow that to be generated > server-side, so that it is available to any and all clients. I > think we should join the crowd in this respect. I could see some things being done this way, but the entire \dt output for a given table strikes me as stretching it pretty far.. And only doing it half-way doesn't strike me as a very good idea. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
Hi Kevin, On Sunday 18 July 2010 21:24:25 Kevin Grittner wrote: > Stefan Kaltenbrunner wrote: > > On 07/18/2010 08:58 PM, Andres Freund wrote: > >> I am quite a bit surprised about all this discussion. I have a > >> very hard time we will find anything people agree about and can > >> remember well enough to be usefull for both manual and automatic > >> processing. > >> > >> I agree that the internal pg_* tables are not exactly easy to > >> query. And that the information_schema. ones arent complete > >> enough and have enough concept mismatch to be confusing. But why > >> all this? > > > > exactly my thoughts - but as I said earlier maybe this is actually > > an opportunity to look at newsysviews again? > > I can't picture anything which could be done with views which would > allow me to issue one statement and see everything of interest about > a table (etc.). You know: tablespace, owner, permissions, columns, > primary key, foreign keys, check constraints, exclusion constraints, > ancestor tables, child tables, and whatever interesting features I > missed or we later add. Other products allow that to be generated > server-side, so that it is available to any and all clients. I > think we should join the crowd in this respect. Such tables sure do not fit queries as in On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: > SHOW ANY TABLE > GROUP BY tablename > HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; At least I dont see any way how you could define aggregation or such sensibly here. Thats the part which scares me quite a bit. Andres -- 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] SHOW TABLES
On 07/18/2010 09:00 PM, Kevin Grittner wrote: Dimitri Fontaine wrote: So what we'd need first is a series of named queries, which I think psql provides for. Any solution which only works within psql isn't a solution for a large part of the problem space people are trying to address. One important goal is that if someone spends a day to whip up a GUI query tool (as I did when I first started working in Java), it's easy to get displays like we get from the psql backslash commands (as it was in Sybase, which is what we were using at the time, through sp_help and related stored procedures). yeah but having to call a SP is basically the same as formulating a query - the point really is that it is completely up to the client to think of a suitable representation for the information and the interface for the user to select data. Just implementing something in the server that either shows "everything" (whatever that really is in practice) will very often not match to what the tool really wants. And once we are into "providing something that can do arbitrary stuff like filtering or output manipulation" we are back to where we are - issueing an SQL-query against the catalog. While the four DBAs use psql heavily, the twenty-some programmers and the business analysts all use various GUI tools which either tie in to their normal environments (for example, eclipse) or are web based hacks which probably didn't take much more effort than the above-mentioned GUI hack which I used for about ten years. Backslash commands do them no good whatsoever, nor will any solution which requires psql. It would be nice if when I display information about a table or some other database object, I could copy from my psql session, paste it into an email, and they could replicate the behavior in squirrel (or whatever the heck else they happen to be running). In that case you are not really using the tool per it's primary purpose (ie say a webgui that provides a graphical interpretation of something) but you are back to merely using it as an SQL-commandline client. I really doubt that there is any solution to the general problem as soon as you want filtering and related stuff - and if you only do the limited version people will soon come back and tell you it's not as flexible as was we had before (like backslash commands can do some limited filtering) or reimplementing SQL. 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] SHOW TABLES
On Sun, 2010-07-18 at 20:39 +0200, Dimitri Fontaine wrote: > SHOW TABLE foo; Yes > SHOW TABLES WHERE tablename ~ 'foo'; > > SHOW ANY TABLE > GROUP BY tablename > HAVING array_agg(attributes) @> array['date'::regtype, > 'time'::regtype]; For me, realistically, No. Simplifying SQL should be left to the SQL standards committee. It could certainly use a hand there, but its too big a mountain too climb, for me. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Le 18 juil. 2010 à 21:21, Andres Freund a écrit : > Providing an easy wrapper is something I could agree without much problems > (as > it doesnt touch me). But starting several new toplevel commands which do not > give everything (i.e. the ability to selectively use columns) but still want > to provide a more or less complete query language and should be sensibly > usable in subqueries et al - thats another thing. That would involve > significant parts of the gram.y, some parts of the parse analysis and the > executor for not enough benefit compared to the significant cost. Agreed that wanting 'SHOW' commands to be full blown SQL is somewhat strange and "pushing it". But people on the list wanted to gather ideas on how to do it before deciding its cost is higher than what it's worth, I guess. Regards, -- dim -- 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] SHOW TABLES
Le 18 juil. 2010 à 20:58, Andres Freund a écrit : > On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: >> SHOW ANY TABLE >> GROUP BY tablename >> HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; > Why is that in *any* way better than > > SELECT * > FROM meta.tables > ... There are two questions here I think, really. First is about having meta-data queries in the backend, and we want that because we want it to be easy for everybody to have access to those, whether they choose to use psql or whatever else. Second is about why having SHOW be usable as if it where a "real" SQL query? That's because it's been said that people will certainly want to go further away using the facility. And now they want full SQL. So it seems to me we're now trying to catch 2 birds with a single 'SHOW' stone. -- Dimitri Fontaine PostgreSQL DBA, Architecte -- 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] SHOW TABLES
Le 18 juil. 2010 à 21:00, Kevin Grittner a écrit : > Dimitri Fontaine wrote: > >> So what we'd need first is a series of named queries, which I >> think psql provides for. > > Any solution which only works within psql isn't a solution for a > large part of the problem space people are trying to address. Exactly. It's all about having it in the backend, in an easy to share format. But what kind of facilities are we talking about? For me, those catalog queries psql already implements. I don't think we should offer \d or whatever in the backend as is, but the queries that \d uses should be a SHOW away. Now this subthread is about having a hard coded facility or the full blown SQL atop. -- Dimitri Fontaine PostgreSQL DBA, Architecte -- 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] SHOW TABLES
Stefan Kaltenbrunner wrote: > On 07/18/2010 08:58 PM, Andres Freund wrote: >> I am quite a bit surprised about all this discussion. I have a >> very hard time we will find anything people agree about and can >> remember well enough to be usefull for both manual and automatic >> processing. >> >> I agree that the internal pg_* tables are not exactly easy to >> query. And that the information_schema. ones arent complete >> enough and have enough concept mismatch to be confusing. But why >> all this? > > exactly my thoughts - but as I said earlier maybe this is actually > an opportunity to look at newsysviews again? I can't picture anything which could be done with views which would allow me to issue one statement and see everything of interest about a table (etc.). You know: tablespace, owner, permissions, columns, primary key, foreign keys, check constraints, exclusion constraints, ancestor tables, child tables, and whatever interesting features I missed or we later add. Other products allow that to be generated server-side, so that it is available to any and all clients. I think we should join the crowd in this respect. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Hi, On Sunday 18 July 2010 21:02:59 Rob Wultsch wrote: > On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund wrote: > > On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: > >> SHOW ANY TABLE > >> GROUP BY tablename > >> HAVING array_agg(attributes) @> array['date'::regtype, > >> 'time'::regtype]; > > > > Why is that in *any* way better than > > > > SELECT * > > FROM meta.tables > > ... > > > > Oh. The second looks like something I know. Oh. My editor maybe as well? > > Oh. And some other tools also? > > > > Your syntax also forgets that maybe I only need a subset of the > > information. > > > > I am quite a bit surprised about all this discussion. I have a very hard > > time we will find anything people agree about and can remember well > > enough to be usefull for both manual and automatic processing. > > > > I agree that the internal pg_* tables are not exactly easy to query. And > > that the information_schema. ones arent complete enough and have enough > > concept mismatch to be confusing. But why all this? > Do you have an alternative suggestion for emulating > "SHOW SCHEMAS" > "SHOW TABLES" > "DESC object"? I personally still fail to see the point of emulating it. Maybe building a short wrapper pointing to the docs or whatever. But thats not the point. Providing an easy wrapper is something I could agree without much problems (as it doesnt touch me). But starting several new toplevel commands which do not give everything (i.e. the ability to selectively use columns) but still want to provide a more or less complete query language and should be sensibly usable in subqueries et al - thats another thing. That would involve significant parts of the gram.y, some parts of the parse analysis and the executor for not enough benefit compared to the significant cost. > Make a user friendly interface is not easy, but it sure as heck is > important. >From my pov making it easier to query the system (either through functions or views) is a worthwile goal though, dont misunderstand me. Andres -- 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] SHOW TABLES
On 07/18/2010 08:58 PM, Andres Freund wrote: On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? exactly my thoughts - but as I said earlier maybe this is actually an opportunity to look at newsysviews again? 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] SHOW TABLES
On Sun, Jul 18, 2010 at 11:58 AM, Andres Freund wrote: > On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: >> SHOW ANY TABLE >> GROUP BY tablename >> HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; > Why is that in *any* way better than > > SELECT * > FROM meta.tables > ... > > Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. > And some other tools also? > > Your syntax also forgets that maybe I only need a subset of the information. > > I am quite a bit surprised about all this discussion. I have a very hard time > we will find anything people agree about and can remember well enough to be > usefull for both manual and automatic processing. > > I agree that the internal pg_* tables are not exactly easy to query. And that > the information_schema. ones arent complete enough and have enough concept > mismatch to be confusing. But why all this? > > Andres > Do you have an alternative suggestion for emulating "SHOW SCHEMAS" "SHOW TABLES" "DESC object"? Make a user friendly interface is not easy, but it sure as heck is important. -- 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] SHOW TABLES
Dimitri Fontaine wrote: > So what we'd need first is a series of named queries, which I > think psql provides for. Any solution which only works within psql isn't a solution for a large part of the problem space people are trying to address. One important goal is that if someone spends a day to whip up a GUI query tool (as I did when I first started working in Java), it's easy to get displays like we get from the psql backslash commands (as it was in Sybase, which is what we were using at the time, through sp_help and related stored procedures). While the four DBAs use psql heavily, the twenty-some programmers and the business analysts all use various GUI tools which either tie in to their normal environments (for example, eclipse) or are web based hacks which probably didn't take much more effort than the above-mentioned GUI hack which I used for about ten years. Backslash commands do them no good whatsoever, nor will any solution which requires psql. It would be nice if when I display information about a table or some other database object, I could copy from my psql session, paste it into an email, and they could replicate the behavior in squirrel (or whatever the heck else they happen to be running). -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Sunday 18 July 2010 20:39:07 Dimitri Fontaine wrote: > SHOW ANY TABLE > GROUP BY tablename > HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; Why is that in *any* way better than SELECT * FROM meta.tables ... Oh. The second looks like something I know. Oh. My editor maybe as well? Oh. And some other tools also? Your syntax also forgets that maybe I only need a subset of the information. I am quite a bit surprised about all this discussion. I have a very hard time we will find anything people agree about and can remember well enough to be usefull for both manual and automatic processing. I agree that the internal pg_* tables are not exactly easy to query. And that the information_schema. ones arent complete enough and have enough concept mismatch to be confusing. But why all this? Andres -- 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] Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
On 07/18/2010 11:41 AM, Kevin Grittner wrote: > > I'm attaching a fresh patch, but I think the only differences are: Thanks for the detailed info. I managed to make my way through much of the background info in the papers and wiki yesterday, so I will start reviewing shortly. > If you spot anything on the Serializable Wiki page which is unclear, > please feel free to fix it or let me know. I'm hoping to ultimately > draw from that for a README file. Sounds good -- exactly what I was thinking as I reviewed it. Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Sunday 18 July 2010 19:20:25 Tom Lane wrote: > Andres Freund writes: > > On Sunday 18 July 2010 18:02:26 Simon Riggs wrote: > >> Then I think the fix is to look at the xmin values on all of the tables > >> used during planning and ensure that we only use constraint-based > >> optimisations in a serializable transaction when our top xmin is later > >> than the last DDL change (via its xmin). > > > > Why not just use a the normal snapshot at that point? > There isn't a "normal snapshot" that the planner should be relying on. > It doesn't know what snap the resulting plan will be used with. Ok, I will write more stupid stuff in the next paragraph. Feel free to ignore. What I meant was to use * the transactions snapshot if we are in a transaction while planning * SnapshotNow otherwise (not sure if thats a situation really existing - I yet have no idea how such utitlity statements are handled snapshot-wise) The errors I described shouldn't matter for an already existing plan. Also the problem with a stale plan is already existing (only slightly aggravated due to the change) and should be handled via plan invalidation. Right? Phantasizing: If you continued with that you even could allow read only access to tables during ALTER TABLE et al. if the actual unlinking of the old filerelnode would get moved to the checkpoint or similar... > I'm unconvinced that this is a problem worth worrying about, but if it > is then Simon's probably got the right idea: check the xmin of a > pg_constraint row before depending on it for planning. Compare the > handling of indexes made with CREATE INDEX CONCURRENTLY. I am happy enough to write a docpatch for those issues and leave it there. Andres -- 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] SHOW TABLES
Hi, Le 18 juil. 2010 à 05:41, Robert Haas a écrit : > On Sat, Jul 17, 2010 at 11:14 PM, Bruce Momjian wrote: >> I am concerned that implementing a command syntax to show complex output >> like above effectively means re-implementing a subset of SQL, and that >> subset will never be as flexible. > > That's a reasonable concern, but I don't have a better idea. Do you? I think that SHOW could be some syntax sugar atop the current rewrite rules system. I mean it would be implemented by means of "parametrized" views. It could be that SQL only SRFs could do a better job at it. In both cases the idea is that we should be able to write SELECT like statements. SHOW TABLE foo; SHOW TABLES WHERE tablename ~ 'foo'; SHOW ANY TABLE GROUP BY tablename HAVING array_agg(attributes) @> array['date'::regtype, 'time'::regtype]; The last one has an "english like" trick using ANY rather than ALL, but that's just for the bikesheding of it, and would list all tables with both a date and a time column. The trick is there because if you want the attributes to show up you're after enhancing the SHOW TABLE query, not the SHOW TABLES one. So what we'd need first is a series of named queries, which I think psql provides for. Then some technique to have them available both as plain and easy usage and in full SQL. I think the rewrite system is meant to allow that, I'm not sure if using views or pure SQL SRFs is better, in both cases the rewritten query has to provide arguments "placeholders": if a VIEW, that's a WHERE clause, if a SRF, any number of named arguments. Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql \conninfo command (was: Patch: psql \whoami option)
On Jul 18, 2010, at 12:33 PM, David Christensen wrote: > > On Jul 18, 2010, at 12:30 PM, Tom Lane wrote: > >> David Christensen writes: >>> machack:machack:5432=# \c "foo""bar" >>> You are now connected to database "foo"bar". >> >> What this is reflecting is that backslash commands have their own weird >> rules for processing double quotes. What I was concerned about was that >> double quotes in SQL are normally used for protecting mixed case, and >> you don't need that for \c: >> >> regression=# create database "FooBar"; >> CREATE DATABASE >> regression=# \c foobar >> FATAL: database "foobar" does not exist >> Previous connection kept >> regression=# \c FooBar >> You are now connected to database "FooBar". >> FooBar=# >> >> The fact that there are double quotes around the database name in the >> "You are now connected..." message is *not* meant to imply that that is >> a valid double-quoted SQL identifier, either. It's just an artifact of >> how we set off names in English-language message style. In another >> language it might look like <> or some such. >> >> My opinion remains that you should just print the user and database >> names as-is, without trying to inject any quoting into the mix. You're >> more likely to confuse people than help them if you do that. > > > Okay, understood. Then consider my updated patch (just sent attached to a > recent message) to reflect the desired behavior. (I'll update the commitfest > patch entry when it shows up in the archives.) Updated the commitfest entry with the patch, updated the title to reflect the actual name of the command, and marked as ready for committer. Regards, David -- David Christensen End Point Corporation da...@endpoint.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] Patch: psql \whoami option
On Jul 18, 2010, at 12:30 PM, Tom Lane wrote: > David Christensen writes: >> machack:machack:5432=# \c "foo""bar" >> You are now connected to database "foo"bar". > > What this is reflecting is that backslash commands have their own weird > rules for processing double quotes. What I was concerned about was that > double quotes in SQL are normally used for protecting mixed case, and > you don't need that for \c: > > regression=# create database "FooBar"; > CREATE DATABASE > regression=# \c foobar > FATAL: database "foobar" does not exist > Previous connection kept > regression=# \c FooBar > You are now connected to database "FooBar". > FooBar=# > > The fact that there are double quotes around the database name in the > "You are now connected..." message is *not* meant to imply that that is > a valid double-quoted SQL identifier, either. It's just an artifact of > how we set off names in English-language message style. In another > language it might look like <> or some such. > > My opinion remains that you should just print the user and database > names as-is, without trying to inject any quoting into the mix. You're > more likely to confuse people than help them if you do that. Okay, understood. Then consider my updated patch (just sent attached to a recent message) to reflect the desired behavior. (I'll update the commitfest patch entry when it shows up in the archives.) Thanks, David -- David Christensen End Point Corporation da...@endpoint.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] Patch: psql \whoami option
David Christensen writes: > machack:machack:5432=# \c "foo""bar" > You are now connected to database "foo"bar". What this is reflecting is that backslash commands have their own weird rules for processing double quotes. What I was concerned about was that double quotes in SQL are normally used for protecting mixed case, and you don't need that for \c: regression=# create database "FooBar"; CREATE DATABASE regression=# \c foobar FATAL: database "foobar" does not exist Previous connection kept regression=# \c FooBar You are now connected to database "FooBar". FooBar=# The fact that there are double quotes around the database name in the "You are now connected..." message is *not* meant to imply that that is a valid double-quoted SQL identifier, either. It's just an artifact of how we set off names in English-language message style. In another language it might look like <> or some such. My opinion remains that you should just print the user and database names as-is, without trying to inject any quoting into the mix. You're more likely to confuse people than help them if you do that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: psql \whoami option
On Jul 18, 2010, at 12:17 PM, David Christensen wrote: > > On Jun 21, 2010, at 9:00 AM, Tom Lane wrote: > >> Robert Haas writes: >>> On Sun, Jun 20, 2010 at 10:51 PM, Steve Singer >>> wrote: One comment I have on the output format is that values (ie the database name) are enclosed in double quotes but the values being quoted can contain double quotes that are not being escaped. >> >> This is the same as standard practice in just about every other >> message... >> >>> It seems like for user and database it might be sensible to apply >>> PQescapeIdentifier to the value before printing it. >> >> I think this would actually be a remarkably bad idea in this particular >> instance, because in the majority of cases psql does not apply >> identifier dequoting rules to user and database names. What is printed >> should be the same as what you'd need to give to \connect, for example. > > > So I'm not quite sure how the above two paragraphs resolve? Should the > user/database names be quoted or not? I have a new version of this patch > available which has incorporated the feedback to this point? > > As an example of the current behavior, consider: > > machack:machack:5432=# create database "foo""bar" > machack-# ; > CREATE DATABASE > > [Sun Jul 18 12:14:49 CDT 2010] > machack:machack:5432=# \c foo"bar > unterminated quoted string > You are now connected to database "machack". > > [Sun Jul 18 12:14:53 CDT 2010] > machack:machack:5432=# \c "foo"bar" > unterminated quoted string > You are now connected to database "machack". > > [Sun Jul 18 12:14:59 CDT 2010] > machack:machack:5432=# \c "foo""bar" > You are now connected to database "foo"bar". > > As you can see, the value passed to connect differs from the output in the > "connected to database" string. It's helpful when you attach said patch. This has been rebased to current HEAD. Regards, David -- David Christensen End Point Corporation da...@endpoint.com psql-conninfo-v2.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Kevin Grittner wrote: Comment style seems to be defined here: http://developer.postgresql.org/pgdocs/postgres/source-format.html as being: /* * comment text begins here * and continues here */ You have these formats in your patch: /* comment text begins here * and continues here */ /* comment text begins here and continues here */ /* One line comment like this. */ That last one is actually pretty common in PostgreSQL source, so I'm not sure that its omission from the style page isn't accidental. The style doc talks about a standard for multi-line comments - it doesn't forbid single line comments. 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
Andres Freund writes: > On Sunday 18 July 2010 18:02:26 Simon Riggs wrote: >> Then I think the fix is to look at the xmin values on all of the tables >> used during planning and ensure that we only use constraint-based >> optimisations in a serializable transaction when our top xmin is later >> than the last DDL change (via its xmin). > Why not just use a the normal snapshot at that point? There isn't a "normal snapshot" that the planner should be relying on. It doesn't know what snap the resulting plan will be used with. I'm unconvinced that this is a problem worth worrying about, but if it is then Simon's probably got the right idea: check the xmin of a pg_constraint row before depending on it for planning. Compare the handling of indexes made with CREATE INDEX CONCURRENTLY. 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] Review: Row-level Locks & SERIALIZABLE transactions, postgres vs. Oracle
Florian Pflug wrote: > On Jul17, 2010, at 18:25 , Kevin Grittner wrote: >> * Does it follow the project coding guidelines? >> >> Comments are not all in standard style. > Does that refer to the language used, or to the formatting? Formatting. Comment style seems to be defined here: http://developer.postgresql.org/pgdocs/postgres/source-format.html as being: /* * comment text begins here * and continues here */ You have these formats in your patch: /* comment text begins here * and continues here */ /* comment text begins here and continues here */ /* One line comment like this. */ That last one is actually pretty common in PostgreSQL source, so I'm not sure that its omission from the style page isn't accidental. > Btw, while the patch obsoletes the crosscheck snapshot, it > currently doesn't remove its traces of it throughout the executor > and the ri triggers. Mainly because I felt doing so would make > forward-porting and reviewing harder without any gain. But > ultimately, those traces should probably all go, unless someone > feels that for some #ifdef NOT_USED is preferable. My view is that we have a revision control system which makes the code easy to restore, should it be found to be useful again. If it has no use at the point of applying this patch, my inclination would be to delete it. If you're particularly concerned that it could later be useful, you might want to do that deletion in as a separate patch to facilitate later resurrection of the code. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: psql \whoami option
On Jun 21, 2010, at 9:00 AM, Tom Lane wrote: > Robert Haas writes: >> On Sun, Jun 20, 2010 at 10:51 PM, Steve Singer >> wrote: >>> One comment I have on the output format is that values (ie the database >>> name) are enclosed in double quotes but the values being quoted can contain >>> double quotes that are not being escaped. > > This is the same as standard practice in just about every other > message... > >> It seems like for user and database it might be sensible to apply >> PQescapeIdentifier to the value before printing it. > > I think this would actually be a remarkably bad idea in this particular > instance, because in the majority of cases psql does not apply > identifier dequoting rules to user and database names. What is printed > should be the same as what you'd need to give to \connect, for example. So I'm not quite sure how the above two paragraphs resolve? Should the user/database names be quoted or not? I have a new version of this patch available which has incorporated the feedback to this point? As an example of the current behavior, consider: machack:machack:5432=# create database "foo""bar" machack-# ; CREATE DATABASE [Sun Jul 18 12:14:49 CDT 2010] machack:machack:5432=# \c foo"bar unterminated quoted string You are now connected to database "machack". [Sun Jul 18 12:14:53 CDT 2010] machack:machack:5432=# \c "foo"bar" unterminated quoted string You are now connected to database "machack". [Sun Jul 18 12:14:59 CDT 2010] machack:machack:5432=# \c "foo""bar" You are now connected to database "foo"bar". As you can see, the value passed to connect differs from the output in the "connected to database" string. Regards, David -- David Christensen End Point Corporation da...@endpoint.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] standard_conforming_strings
"David E. Wheeler" writes: > On Jul 18, 2010, at 1:35 AM, Peter Eisentraut wrote: >> I think there are two ways we can do this, seeing that most appear to be >> in favor of doing it in the first place: Either we just flip the >> default, make a note in the release notes, and see what happens. Or we >> spend some time now and make, say, a list of driver versions and >> application versions that work with standard_conforming_strings = on, >> and then decide based on that, and also make that list a public resource >> for packagers etc. > Do both. Turn them on, then make a list and inform driver maintainers who > need to update. They've got a year, after all. Yeah. If we wait for driver authors to do something, we'll never make this change at all. The idea of committing it now is to give them a shove, *and* enough time to respond. 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] Fix for libpq compile
Bruce Momjian writes: > I have applied the attached check, but without SO_PEERCRED I can't > report the username which failed. Please adjust this if necessary. AFAICT that should have been using the uid variable, not any of the platform-specific things. Apparently this was not tested before commit on any but SO_PEERCRED platforms. 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 TABLE SET STATISTICS requires AccessExclusiveLock
On Sunday 18 July 2010 18:02:26 Simon Riggs wrote: > On Sun, 2010-07-18 at 17:28 +0200, Andres Freund wrote: > > Unfortunately the same issue exists with constraint exclusion - and we > > can hardly disable that for serializable transactions... > > Then I think the fix is to look at the xmin values on all of the tables > used during planning and ensure that we only use constraint-based > optimisations in a serializable transaction when our top xmin is later > than the last DDL change (via its xmin). Why not just use a the normal snapshot at that point? Any older constraints should be just as valid for the tuples visible for the to-be-planned query. I also think that would lay groundwork for reducing lock-levels further in the future. Andres -- 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] standard_conforming_strings
On Jul 18, 2010, at 1:35 AM, Peter Eisentraut wrote: > I think there are two ways we can do this, seeing that most appear to be > in favor of doing it in the first place: Either we just flip the > default, make a note in the release notes, and see what happens. Or we > spend some time now and make, say, a list of driver versions and > application versions that work with standard_conforming_strings = on, > and then decide based on that, and also make that list a public resource > for packagers etc. Do both. Turn them on, then make a list and inform driver maintainers who need to update. They've got a year, after all. 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] crash-recovery replay of CREATE TABLESPACE is broken in HEAD
Bruce Momjian wrote:
> Tom Lane wrote:
> > Heikki Linnakangas writes:
> > > Maybe you should check that it points to the right location? Or drop and
> > > recreate the symlink, and ignore failure at mkdir.
> >
> > More specifically, ignore EEXIST failure when replaying mkdir. Anything
> > else is still a problem.
>
> Thanks for the help. I tried to find somewhere else in our recovery
> code that was similar but didn't find anything.
>
> The attached patch does as suggested. I added the recovery code to the
> create tablespace function so I didn't have to duplicate all the code
> that computes the path names.
>
> Attached.
Uh, another question. Looking at the createdb recovery, I see:
/*
* Our theory for replaying a CREATE is to forcibly drop the target
* subdirectory if present, then re-copy the source data. This may be
* more work than needed, but it is simple to implement.
*/
if (stat(dst_path, &st) == 0 && S_ISDIR(st.st_mode))
{
if (!rmtree(dst_path, true))
ereport(WARNING,
(errmsg("some useless files may be left behind in old
database directory \"%s\"",
dst_path)));
}
Should I be using rmtree() on the mkdir target?
Also, the original tablespace recovery code did not drop the symlink
first. I assume that was not a bug only because we don't support moving
tablespaces:
- /* Create the symlink if not already present */
- linkloc = (char *) palloc(OIDCHARS + OIDCHARS + 1);
- sprintf(linkloc, "pg_tblspc/%u", xlrec->ts_id);
-
- if (symlink(location, linkloc) < 0)
- {
- if (errno != EEXIST)
- ereport(ERROR,
- (errcode_for_file_access(),
-errmsg("could not create
symbolic link \"%s\": %m",
- linkloc)));
- }
Still, it seems logical to unlink it before creating it.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
--
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 SET STATISTICS requires AccessExclusiveLock
On Sun, 2010-07-18 at 17:28 +0200, Andres Freund wrote: > Unfortunately the same issue exists with constraint exclusion - and we > can hardly disable that for serializable transactions... Then I think the fix is to look at the xmin values on all of the tables used during planning and ensure that we only use constraint-based optimisations in a serializable transaction when our top xmin is later than the last DDL change (via its xmin). -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] crash-recovery replay of CREATE TABLESPACE is broken in HEAD
Tom Lane wrote:
> Heikki Linnakangas writes:
> > Maybe you should check that it points to the right location? Or drop and
> > recreate the symlink, and ignore failure at mkdir.
>
> More specifically, ignore EEXIST failure when replaying mkdir. Anything
> else is still a problem.
Thanks for the help. I tried to find somewhere else in our recovery
code that was similar but didn't find anything.
The attached patch does as suggested. I added the recovery code to the
create tablespace function so I didn't have to duplicate all the code
that computes the path names.
Attached.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: src/backend/commands/tablespace.c
===
RCS file: /cvsroot/pgsql/src/backend/commands/tablespace.c,v
retrieving revision 1.77
diff -c -c -r1.77 tablespace.c
*** src/backend/commands/tablespace.c 18 Jul 2010 04:47:46 - 1.77
--- src/backend/commands/tablespace.c 18 Jul 2010 15:53:34 -
***
*** 568,578
*/
if (mkdir(location_with_version_dir, S_IRWXU) < 0)
{
if (errno == EEXIST)
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_IN_USE),
! errmsg("directory \"%s\" already in use as a tablespace",
! location_with_version_dir)));
else
ereport(ERROR,
(errcode_for_file_access(),
--- 568,582
*/
if (mkdir(location_with_version_dir, S_IRWXU) < 0)
{
+ /* In recovery, directory might already exists, which is OK */
if (errno == EEXIST)
! {
! if (!InRecovery)
! ereport(ERROR,
! (errcode(ERRCODE_OBJECT_IN_USE),
! errmsg("directory \"%s\" already in use as a tablespace",
! location_with_version_dir)));
! }
else
ereport(ERROR,
(errcode_for_file_access(),
***
*** 580,585
--- 584,599
location_with_version_dir)));
}
+ /* Remove old symlink in recovery, in case it points to the wrong place */
+ if (InRecovery)
+ {
+ if (unlink(linkloc) < 0 && errno != ENOENT)
+ ereport(ERROR,
+ (errcode_for_file_access(),
+ errmsg("could not remove symbolic link \"%s\": %m",
+ linkloc)));
+ }
+
/*
* Create the symlink under PGDATA
*/
--
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 libpq compile
This commit caused my compile to fail on CVS HEAD:
revision 1.399
date: 2010/07/18 11:37:26; author: petere; state: Exp; lines: +88 -1
Add server authentication over Unix-domain sockets
This adds a libpq connection parameter requirepeer that specifies the
user
name that the server process is expected to run under.
reviewed by KaiGai Kohei
The problem is that the code was not checking for SO_PEERCRED.
I have applied the attached check, but without SO_PEERCRED I can't
report the username which failed. Please adjust this if necessary.
Thanks.
--
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com
+ None of us is going to be here forever. +
Index: fe-connect.c
===
RCS file: /cvsroot/pgsql/src/interfaces/libpq/fe-connect.c,v
retrieving revision 1.399
diff -c -r1.399 fe-connect.c
*** fe-connect.c 18 Jul 2010 11:37:26 - 1.399
--- fe-connect.c 18 Jul 2010 15:47:32 -
***
*** 1816,1823
--- 1816,1827
if (pass == NULL)
{
appendPQExpBuffer(&conn->errorMessage,
+ # if defined(SO_PEERCRED)
libpq_gettext("local user with ID %d does not exist\n"),
(int) peercred.uid);
+ #else
+ libpq_gettext("matching local user does not exist\n"));
+ #endif
goto error_return;
}
--
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 SET STATISTICS requires AccessExclusiveLock
On Saturday 17 July 2010 09:55:37 Simon Riggs wrote: > On Fri, 2010-07-16 at 23:03 +0200, Andres Freund wrote: > > Sure its not that bad, but at least it needs to get documented imho. > > Likely others should chime in here ;-) > > Don't understand you. This is a clear bug in join removal, test case > attached, a minor rework of your original test case. As shown below the same issue exists in other codepaths that we cant easily fix in a stable release :-( - so I think documenting it is the only viable action for the back-branches. > > What could the join removal path (and similar places) *possibly* do > > against such a case? Without stopping to use SnapshotNow I dont see > > any way :-( > The bug is caused by allowing join removal to work in serializable > transactions. The fix for 9.0 is easy and clear: disallow join removal > when planning a query as the second or subsequent query in a > serializable transaction. > > A wider fix might be worth doing for 9.1, not sure. Unfortunately the same issue exists with constraint exclusion - and we can hardly disable that for serializable transactions... CREATE TABLE testconstr(data int); INSERT INTO testconstr VALUES(1),(10); T1: test=# explain analyze SELECT * FROM testconstr WHERE data > 5; QUERY PLAN --- Seq Scan on testconstr (cost=0.00..40.00 rows=800 width=4) (actual time=0.029..0.032 rows=1 loops=1) Filter: (data > 5) Total runtime: 0.097 ms (3 rows) test=# BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; BEGIN --make sure we do have a snapshot test=# SELECT * FROM pg_class WHERE 0 = 1 T2: DELETE FROM testconstr WHERE data >= 5; ALTER TABLE testconstr ADD CONSTRAINT t CHECK(data < 5); T1: test=# explain analyze SELECT * FROM testconstr WHERE data > 5; QUERY PLAN Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.003..0.003 rows=0 loops=1) One-Time Filter: false Total runtime: 0.045 ms (3 rows) test=# SET constraint_exclusion = false; SET test=# explain analyze SELECT * FROM testconstr WHERE data > 5; QUERY PLAN --- Seq Scan on testconstr (cost=0.00..40.00 rows=800 width=4) (actual time=0.030..0.033 rows=1 loops=1) Filter: (data > 5) Total runtime: 0.099 ms (3 rows) Thats seems to be an issue that you realistically can hit in production... I think the same problem exists with inheritance planning - i.e. a child table added to a relation in T1 while T2 already holds a snapshot but hasnt used that specific table was created will see the new child. Thats less severe but still annoying. Beside using an actual Snapshot in portions of the planner (i.e. stats should continue using SnapshotNow) I dont really see a fix here. Andres Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Parsing of aggregate ORDER BY clauses
I looked into the problem reported here: http://archives.postgresql.org/pgsql-bugs/2010-07/msg00119.php The reason it's failing is that when parse_agg.c calls transformSortClause() to process the ORDER BY, the latter function fails to match the "t" in ORDER BY to the one in the function's input argument list. And the reason it fails is that parse_func.c already coerced the arguments to be what the function expects. So rather than a plain Var for the varchar column "t", the argument list contains "t::text", which isn't equal() to "t". The same type of thing would happen in any case where implicit coercion of the arguments was needed to produce the exact data type expected by the aggregate. I thought of a few ways to attack this, most of which don't look very workable: 1. Postpone coercion of the function inputs till after processing of the ORDER BY/DISTINCT decoration. This isn't too good because then we'll be using the "wrong" data type for deciding the semantics of ORDER BY/DISTINCT. That could lead to bizarre behavior or even crashes, eg if we try to use numeric sort operators on a value that actually has been coerced to float8. We could possibly go back and re-do the decisions about data types but it'd be a mess. 2. Split the processing of aggregates with ORDER BY/DISTINCT so that the sorting/uniqueifying is done in a separate expression node that can work with the "native" types of the given columns, and only after that do we perform coercion to the aggregate function's input types. This would be logically the cleanest thing, perhaps, but it'd represent a very major rework of the patch, with really no hope of getting it done for 9.0. 3. Do something so that we can still match "t::text" to "t". This seems pretty awful on first glance but it's not actually that bad, because in the case we care about the cast will be marked as having been applied implicitly. Basically, instead of just equal() comparisons in findTargetlistEntrySQL99(), we'd strip off any implicit cast at the top of either expression, and only then do equal(). Since the implicit casts are, by definition, things the user didn't write, this would still have the expected behavior of matching expressions that were identical when the user wrote them. #3 seems the sanest fix, but I wonder if anyone has an objection or better idea. 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] Review: Patch for phypot - Pygmy Hippotause
Dean Rasheed writes: > No. If x is 1e8 * y, then y will only affect the result in the 16th > place. You can see this if you do a simple series expansion: > sqrt(1+yx^2) = 1 + 1/2 yx^2 + O(yx^4) Sigh, I went looking for that expansion yesterday and didn't find it. Should've tried harder. I was relying on a gut feeling that it would behave approximately like ln(1+x). > For most cases, the new algorithm is no more accurate than the old > one. The exception is when *both* x and y are very small. In this > case, it protects against incorrect underflows to 0. Yeah, I think you're right. 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] dividing money by money
Peter Eisentraut writes: > I have never used the money type, so I'm not in a position to argue what > might be typical use cases, but it is well understood that using > floating-point arithmetic anywhere in calculations involving money is > prohibited by law or business rules in most places. So when I read that > multiplications or divisions involving the money type use float, to me > that means the same as "never use the money type, it's broken". [ shrug... ] A lot of people think that about the money type, all for different reasons. This particular argument seems tissue-thin to me, mainly because the same people who complain "it must be exact" have no problem rounding off their results to the nearest pfennig or whatever. Also, you seem not to have absorbed the fact that changing the output to numeric *will not make the result exact anyway*. If the point of a business rule of this sort is to prohibit inexact calculations, then having it flag cash / cash as inexact is a Good Thing. 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] crash-recovery replay of CREATE TABLESPACE is broken in HEAD
Heikki Linnakangas writes: > Maybe you should check that it points to the right location? Or drop and > recreate the symlink, and ignore failure at mkdir. More specifically, ignore EEXIST failure when replaying mkdir. Anything else is still a problem. 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] dividing money by money
On lör, 2010-07-17 at 10:00 -0500, Kevin Grittner wrote: > True. If we added money * numeric, then it would make more sense to > have money / money return numeric. On the other hand, I couldn't > come up with enough use cases for that to feel that it justified the > performance hit on money / money for typical use cases -- you > normally want a ratio for things where float8 is more than > sufficient; and you can always cast the arguments to numeric for > calculations where the approximate result isn't good enough. > Basically, once we agreed to include casts to and from numeric, it > seemed to me we had it covered. I have never used the money type, so I'm not in a position to argue what might be typical use cases, but it is well understood that using floating-point arithmetic anywhere in calculations involving money is prohibited by law or business rules in most places. So when I read that multiplications or divisions involving the money type use float, to me that means the same as "never use the money type, it's broken". -- 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] dividing money by money
On lör, 2010-07-17 at 07:20 -0700, Andy Balholm wrote: > On Jul 17, 2010, at 3:20 AM, Peter Eisentraut wrote: > > > On fre, 2010-07-16 at 10:31 -0400, Tom Lane wrote: > >> The other argument that I found convincing was that if the > >> operator was defined to yield numeric, people might think that > >> the result was exact ... which of course it won't be, either way. > >> Choosing float8 helps to remind the user it's an approximate quotient. > > > > Why is it approximate? Aren't money values really integers? > > $1.00 / 3.00 = 0.... By that reasoning, numeric / numeric should also yield float. -- 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] Functional dependencies and GROUP BY
On lör, 2010-07-17 at 11:13 -0600, Alex Hunsaker wrote: > its really no surprise that your test with 1600 columns had little > effect. As it loops over the the indexes, then the index keys and > then the group by items right? So I would expect the more indexes you > had or group by items to slow it down. Not so much the number of > columns. Right? At the outer level (which is not visible in this patch) it loops over all columns in the select list, and then it looks up the indexes each time. So the concern was that if the select list was * with a wide table, looking up the indexes each time would be slow. > Anyhow it sounds like I should try it on top of the other patch and > see if it works. I assume it might still need some fixups to work > with that other patch? Or do you expect it to just work? There is some work necessary to integrate the two. -- 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] standard_conforming_strings
On ons, 2010-07-14 at 10:48 -0400, Robert Haas wrote: > On Fri, Jan 29, 2010 at 10:02 PM, Josh Berkus wrote: > >> An actual plan here might look like "let's flip it before 9.1alpha1 > >> so we can get some alpha testing cycles on it" ... > > > > "Hey, let's flip it in 9.1 CF 1, so that we can have some alpha testing > > cycles on it." > > Should we do this? Patch attached. I think there are two ways we can do this, seeing that most appear to be in favor of doing it in the first place: Either we just flip the default, make a note in the release notes, and see what happens. Or we spend some time now and make, say, a list of driver versions and application versions that work with standard_conforming_strings = on, and then decide based on that, and also make that list a public resource for packagers etc. -- 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] Review: Patch for phypot - Pygmy Hippotause
On 17 July 2010 20:19, Tom Lane wrote: > ... For instance, if x is 1e8 * y, then y*y > fails to affect the sum at all (given typical float8 arithmetic), and > you'll get back sqrt(x*x) even though y should have been able to affect > the result at the 8th place or so. In the patch's calculation, y/x is > computed accurately but then we'll lose the same precision when we form > 1 + yx*yx --- the result will be just 1 if y is lots smaller than x. > No. If x is 1e8 * y, then y will only affect the result in the 16th place. You can see this if you do a simple series expansion: sqrt(1+yx^2) = 1 + 1/2 yx^2 + O(yx^4) > If we were feeling tense about this, we could look for an alternate way > of calculating sqrt(1 + yx*yx) that doesn't lose so much accuracy. > In principle I think that's doable since this expression is related to > ln(1+x) which can be calculated accurately even for very small x. This algorithm is about as accurate as it could possibly be. The point with ln(1+x) is that for small x: ln(1+x) = x + O(x^2) so you would loose precision if x were much smaller than 1. This is not the case with sqrt(1+x). For most cases, the new algorithm is no more accurate than the old one. The exception is when *both* x and y are very small. In this case, it protects against incorrect underflows to 0. Regards, Dean -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
