Re: [HACKERS] Per-column collation, proof of concept
On tor, 2010-07-15 at 05:57 +0200, Pavel Stehule wrote: :( maybe we have to enhance a locales - or do some work in this way. In Czech's IS is relative often operation some like name = 'Stěhule' COLLATION cs_CZ_cs_ai -- compare case insensitive accent insensitive PostgreSQL is last db, that doesn't integreated support for it Well, the comparison function varstr_cmp() contains this comment: /* * In some locales strcoll() can claim that nonidentical strings are * equal. Believing that would be bad news for a number of reasons, * so we follow Perl's lead and sort equal strings according to * strcmp(). */ This might not be strictly necessary, seeing that citext obviously doesn't work that way, but resolving this is really an orthogonal issue. If you fix that and you have a locale that does what you want, my patch will help you get your example working. -- Sent 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 Thu, 2010-07-08 at 07:16 +0100, Simon Riggs wrote: I'll take my previous patch through to completion now Patch to reduce lock levels for ALTER TABLE CREATE TRIGGER CREATE RULE I've completely re-analyzed the required lock levels for sub-commands, so lock levels can now also be these, if appropriate. ShareUpdateExclusiveLock - allows db reads and writes ShareRowExclusiveLock - allows db reads only When ALTER TABLE is specified with multiple subcommands the highest lock level required by any subcommand is applied to the whole combined command. The lock levels are in many ways different from both my own earlier patch and much of the discussion on this thread, which I have taken to be general comments rather than considered thought. Nothing much speculative here, so will commit in a few days barring objections. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services *** a/doc/src/sgml/mvcc.sgml --- b/doc/src/sgml/mvcc.sgml *** *** 532,538 SELECT SUM(value) FROM mytab WHERE class = 2; most productnamePostgreSQL/productname commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the ! command executes. (For example, commandALTER TABLE/ cannot safely be executed concurrently with other operations on the same table, so it obtains an exclusive lock on the table to enforce that.) /para --- 532,538 most productnamePostgreSQL/productname commands automatically acquire locks of appropriate modes to ensure that referenced tables are not dropped or modified in incompatible ways while the ! command executes. (For example, commandTRUNCATE/ cannot safely be executed concurrently with other operations on the same table, so it obtains an exclusive lock on the table to enforce that.) /para *** *** 695,702 SELECT SUM(value) FROM mytab WHERE class = 2; /para para ! This lock mode is not automatically acquired by any ! productnamePostgreSQL/productname command. /para /listitem /varlistentry --- 695,703 /para para ! Acquired by commandCREATE TRIGGER/command, ! commandCREATE RULE/command (except for literalON SELECT/ ! rules) and in some cases commandALTER TABLE/command. /para /listitem /varlistentry *** *** 742,752 SELECT SUM(value) FROM mytab WHERE class = 2; /para para ! Acquired by the commandALTER TABLE/command, commandDROP ! TABLE/command, commandTRUNCATE/command, commandREINDEX/command, commandCLUSTER/command, and commandVACUUM FULL/command ! commands. This is also the default lock mode for commandLOCK ! TABLE/command statements that do not specify a mode explicitly. /para /listitem /varlistentry --- 743,754 /para para ! Acquired by the commandDROP TABLE/command, ! commandTRUNCATE/command, commandREINDEX/command, commandCLUSTER/command, and commandVACUUM FULL/command ! commands, as well as most variants of commandALTER TABLE/. ! This is also the default lock mode for commandLOCK TABLE/command ! statements that do not specify a mode explicitly. /para /listitem /varlistentry *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *** *** 64,69 --- 64,70 #include rewrite/rewriteHandler.h #include storage/bufmgr.h #include storage/lmgr.h + #include storage/lock.h #include storage/smgr.h #include utils/acl.h #include utils/builtins.h *** *** 253,273 static void validateForeignKeyConstraint(Constraint *fkconstraint, Oid pkindOid, Oid constraintOid); static void createForeignKeyTriggers(Relation rel, Constraint *fkconstraint, Oid constraintOid, Oid indexOid); ! static void ATController(Relation rel, List *cmds, bool recurse); static void ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, ! bool recurse, bool recursing); ! static void ATRewriteCatalogs(List **wqueue); static void ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, ! AlterTableCmd *cmd); static void ATRewriteTables(List **wqueue); static void ATRewriteTable(AlteredTableInfo *tab, Oid OIDNewHeap); static AlteredTableInfo *ATGetQueueEntry(List **wqueue, Relation rel); static void ATSimplePermissions(Relation rel, bool allowView); static void ATSimplePermissionsRelationOrIndex(Relation rel); static void ATSimpleRecursion(List **wqueue, Relation rel, ! AlterTableCmd *cmd, bool recurse); static void ATOneLevelRecursion(List **wqueue, Relation rel, ! AlterTableCmd *cmd); static void
Re: [HACKERS] cross column correlation revisted
hello ... a view is already nice but i think it is still too narrow. the problem is: you don't want a view for every potential join. in addition to that - ideally there is not much left of a view when it comes to checking for costs. so, i think, this is not the kind of approach leading to total success here. one side question: does anybody happen to know how this is one in oracle or db2? many thanks, hans On Jul 15, 2010, at 1:33 AM, Dimitri Fontaine wrote: Joshua Tolley eggyk...@gmail.com writes: ALTER TABLE x SET CORRELATION STATISTICS FOR (x.id =3D y.id AND x.id= 2 =3D y.id2) =20 it says X and Y ... the selectivity of joins are what i am most interested in. cross correlation of columns within the same table are just a byproduct. the core thing is: how can i estimate the number of rows returned from a join? All the discussion of this topic that I've seen has been limited to the s= ingle table case. The hard problem in that case is coming up with something you= can precalculate that will actually be useful during query planning, without taking too much disk, memory, CPU, or something else. Expanding the discu= ssion to include join relations certainly still has valid use cases, but is even harder, because you've also got to keep track of precisely how the underl= ying relations are joined, so you know in what context the statistics remain v= alid. Well I've been proposing to handle the correlation problem in another way in some past mails here, and I've been trying to write it down too: http://archives.postgresql.org/pgsql-performance/2009-06/msg00118.php http://tapoueh.org/char10.html#sec13 What I propose is to extend ANALYZE to be able to work on a VIEW too, rather than just a table. The hard parts seems to be: a. what stats to record, exploiting the view definition the best we can b. how to match a user query against the view definitions we have in order to actually use the stats If you have answers or good ideas=C2=A0:) Regards, --=20 dim -- dim -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning syntax
On Tue, 2010-07-06 at 13:49 -0400, Robert Haas wrote: 1. It seems to me that the proposed design for pg_partition is poorly thought out. In particular, I don't see how this would work if we wanted to partition on multiple keys, which is a feature supported by both Oracle and MySQL. It would also be nice to give at least some thought to how we might handle partitioning by list with subpartitioning by range or hash, or range partitioning with subpartitioning by hash. We certainly don't need to do subpartitioning in the first version of the patch, but I think we should have a plan. Or at least a way to store that information if/when it exists later. 2. I am still of the view that the first version of this patch should correctly handle routing of INSERT and COPY data to the correct partition. But at a very minimum we need to have a plan for how we're going to implement that in a follow-on patch. I think the way to do this is to binary search a sorted array of partition keys (perhaps upper bounds for range partitioning, and exact values for list partitioning). When you find the correct key, then you find the index of that key and look up that same index in a separate array of table OIDs and insert there. While it's possible to construct such a structure from the proposed catalog structure, it requires an index scan. I'm wondering if it might be better to abandon the idea of storing the partition values in pg_inherits and instead put preconstructed arrays directly into pg_partition. That way, with a single row fetch, you can get all the data you need. I'm not sure this is better, though - other opinions? Agreed that it is really important. The heart of partitioning is the metadata that will allow us to do insert routing as well as nested joins using dynamic routing. We *must* plan for that so that the command syntax and catalog storage delivers what is required. This patch must not be just about syntax. The required usage drives the syntax, not the other way around. 3. For a first version of this patch, I would suggest that we only allow partitioning by base columns, rather than expressions. When someone goes to do a bulk load of data into the table, and we want to do automatic tuple routing, we're going to have to evaluate the partitioning expression(s) for every row. I'm just guessing here, but I bet it's a lot cheaper to fetch an attribute by attnum than to evaluate an arbitrary expression. So even if we add partitioning by expression later, I don't think that the work to make a special case for base columns will be wasted. Agree that part should come out for now and resubmit as a later patch. Lets keep it simple in the first version. 5. The use of the term partition is not very consistent. For example, we use CREATE PARTITION to create a partition, but we use DROP TABLE to get rid of it (there is no DROP PARTITION). I think that the right syntax to use here is ALTER TABLE ... ADD/DROP PARTITION; both Oracle and MySQL do it that way. And meanwhile OCLASS_PARTITION means the partitioning information associated with the parent table, not a partition of a parent table. Definitely do not want CREATE PARTITION. ALTER TABLE is the best place. 6. There's some kind of magic in here associated with indexes on the parent table - it seems that matching indexes or primary keys are automatically created on each child table. But there's no provision for keeping them in sync. If I create a partitioned table with a primary key, the key is inherited by all its current children. If I then drop the primary key, it disappears from the parent but it still exists on the children. Any new children created afterwards don't have it, however. I'm not sure whether indices should propagate from parent to child or not, but propagating whatever exists at the moment of creation and then forgetting about it doesn't seem right. IMHO it should be optional as to whether all partitions have identical indexing. It is an important aspect of the design that an historical table may have different indexes on different parts of the table, since different users/use cases exist for access to that data. No problem if some people want that though. 7. I'm not convinced that it's a good idea to treat ALTER TABLE parent ATTACH/DETACH PARTITION child as basically a synonym for ALTER TABLE child [NO] INHERIT parent, but even if it is the current implementation seems way too permissive (it also lacks comments and adequate documentation). You can, for example, use ATTACH PARTITION to add a new child and then NO INHERIT to detach it again; or you can use INHERIT to attach a child even when the parent is partitioned. It does however catch the case of trying to use ATTACH PARTITION to attach a child to an unpartitioned parent. Agreed. If all we are doing is adding synonyms for existing feature then its not good enough. We need a new syntax that does not need to be
Re: [HACKERS] standard_conforming_strings
On Jul 15, 2010, at 12:30 AM, Richard Huxton d...@archonet.com wrote: On 14/07/10 15:48, Robert Haas wrote: On Fri, Jan 29, 2010 at 10:02 PM, Josh Berkusj...@agliodbs.com 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. Any reason not to add a line to the 9.0 docs/release notes saying WARNING: The PGDG currently plan to change this setting's default in 9.1? Well, mostly that we could change our mind if it makes too big a boom. And it's not as if we could go back and update everyone's docs after-the-fact. I agree we need some press, but the docs are not the right vehicle. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cross column correlation revisted
On Thu, Jul 15, 2010 at 12:04:21PM +0200, Hans-Jürgen Schönig wrote: hello ... a view is already nice but i think it is still too narrow. the problem is: you don't want a view for every potential join. in addition to that - ideally there is not much left of a view when it comes to checking for costs. so, i think, this is not the kind of approach leading to total success here. The prolem is a very big one, and it's helpful to try solving it one piece at a time, so the single table and view-based cases are probably good starting points. one side question: does anybody happen to know how this is one in oracle or db2? Neither appear to handle multi-column statistics in any form. [1] http://download.oracle.com/docs/cd/B13789_01/appdev.101/b10802/d_stats.htm [2] http://www.ibm.com/developerworks/data/library/techarticle/dm-0606fechner/index.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] bg worker: overview
Hi, We've been talking about this topic on -performance: Markus Wanner mar...@bluegap.ch writes: I've combined these two components into a single, general purpose background worker infrastructure component, which is now capable to serve autovacuum as well as Postgres-R. And it might be of use for other purposes as well, most prominently parallel query processing. Basically anything that needs a backend connected to a database to do any kind of background processing, possibly parallelized. Magnus Hagander mag...@hagander.net writes: On Tue, Jul 13, 2010 at 16:42, Dimitri Fontaine dfonta...@hi-media.com wrote: So a supervisor daemon with a supervisor API that would have to support autovacuum as a use case, then things like pgagent, PGQ and pgbouncer, would be very welcome. What about starting a new thread about that? Or you already know you won't want to push the extensibility of PostgreSQL there? +1 on this idea in general, if we can think up a good API - this seems very useful to me, and you have some good examples there of cases where it'd definitely be a help. So, do you think we could use your work as a base for allowing custom daemon code? I guess we need to think about how to separate external code and internal code, so a second layer could be necessary here. As far as the API goes, I have several ideas but nothing that I have already implemented, so I'd prefer to follow Markus there :) 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] cross column correlation revisted
On Thu, Jul 15, 2010 at 12:04:21PM +0200, Hans-Jürgen Schönig wrote: hello ... a view is already nice but i think it is still too narrow. One sure way to fail is to take on a problem in chunks too large. If we get even one of the cross-column issues solved by statistics, we'll be ahead of all our competition, both free and proprietary. Cheers, David. -- David Fetter da...@fetter.org 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
[HACKERS] CommitFest 2010-07 now in progress
/me bangs gavel I hereby declare the 2010-07 CommitFest closed to further patch submissions, as it is now officially In Progress. We have one month to provide initial review of the patches which have accumulated since the start of the last CommitFest, six months ago, and hopefully get a reasonable number of them committed. All reviewers currently assigned to a patch in Needs Review status should post a review within the next four days. All authors with patches in Waiting on Author status should post a response within four days. If there's a reason that can't happen, please let me know off-list. Some numbers: 68 patches were submitted 3 patches were withdrawn (deleted) by their authors -- 65 total patches currently in the application -- 3 committed to 9.0 -- 62 9.1 patches -- 1 rejected 3 returned with feedback 1 committed for 9.1 -- 57 pending 10 ready for committer -- 47 will still need reviewer attention 6 waiting on author to respond to review -- 41 need review before further action 23 patches Needs Review patches don't have a reviewer assigned -- 18 patches have reviews due within four days We could still use additional reviewers. It's not too late to sign up! To get an idea of what's involved, please read these pages: http://wiki.postgresql.org/wiki/Reviewing_a_Patch http://wiki.postgresql.org/wiki/RRReviewers On the lighter side: http://wiki.postgresql.org/images/5/58/11_eggyknap-patch-review.pdf Please send me an email (without copying the list) if you are available to review; feel free to include any information that might be helpful in assigning you an appropriate patch. To see what patches still need a reviewer, you could scroll through the web application looking at the Reviewers column: http://commitfest.postgresql.org/action/commitfest_view/inprogress -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SHOW TABLES
The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES ... SHOW [FULL] any object type with identical meaning to psql's \d? syntax. Why? Because it will help people, most importantly, new people. It's similar enough to other systems to be useful and user friendly enough to be sensible. The command output will not mimic output from other systems. While I'm on the theme of do the obvious, I'd also like to make psql recognise the word QUIT, in all cases. No, its not April 1, this is a serious and to 1000s of people an obvious thing to help us shine a light inside the black box of Postgres. -- 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
Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. 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] SHOW TABLES
On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- 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] Per-column collation, proof of concept
Peter Eisentraut pete...@gmx.net writes: Well, the comparison function varstr_cmp() contains this comment: /* * In some locales strcoll() can claim that nonidentical strings are * equal. Believing that would be bad news for a number of reasons, * so we follow Perl's lead and sort equal strings according to * strcmp(). */ This might not be strictly necessary, seeing that citext obviously doesn't work that way, but resolving this is really an orthogonal issue. The problem with not doing that is it breaks hashing --- hash joins and hash aggregation being the real pain points. citext works around this in a rather klugy fashion by decreeing that two strings are equal iff their str_tolower() conversions are bitwise equal. So it can hash the str_tolower() representation. But that's kinda slow and it fails in the general case anyhow, I think. 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] SHOW TABLES
On 15 July 2010 16:20, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php Thom -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Partitioning syntax
Simon Riggs si...@2ndquadrant.com writes: Agreed. If all we are doing is adding synonyms for existing feature then its not good enough. We need a new syntax that does not need to be backwards compatible, allowing various code streamlining and more targeting to the desired use case. Inheritance != partitioning. Similar, maybe, but not identical. Probably also the only way we can move forwards without breaking all the existing user code in subtle ways. My feeling about it is that partitioning should be a subset of inheritance --- that is, a partitioned table is an inheritance tree, but with additional constraints/properties/catalog information. In the case at hand, that means that you couldn't use ALTER TABLE INHERIT to install a new partition, but only because it would fail to provide the additional information needed (partition key info). ALTER TABLE ATTACH PARTITION is like INHERIT except it also provides the extra partitioning info. OTOH, DETACH PARTITION is not really significantly different from ALTER NO INHERIT --- you could allow them to be used interchangeably. Though I'd still favor keeping them separate just for consistency of the DDL language. 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] SHOW TABLES
On Jul 15, 2010, at 5:20 PM, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services simon is absolutely right here. we should not mind being a little more user friendly in this area. many people are simply used to this kind of stuff. remember when you rejected something the last time (not necessarily software). was ist because you could not make it work in 2 min or was it because you did not like something else? do you reject buying a car because of a non obvious screw in the engine or because it somehow does not feel right? simon made an important point and i can simply agree - regardless of whether it has been discussed before or not. if you die a beautiful death you are still dead after all. regards, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent 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 Thu, Jul 15, 2010 at 17:30, Thom Brown thombr...@gmail.com wrote: On 15 July 2010 16:20, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? -- 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] standard_conforming_strings
Robert Haas robertmh...@gmail.com writes: On Jul 15, 2010, at 12:30 AM, Richard Huxton d...@archonet.com wrote: Any reason not to add a line to the 9.0 docs/release notes saying WARNING: The PGDG currently plan to change this setting's default in 9.1? Well, mostly that we could change our mind if it makes too big a boom. And it's not as if we could go back and update everyone's docs after-the-fact. Yeah. Our track record for predicting in the version-N docs what changes will be made in version N+1 is spectacularly bad; we should not try that here, even if the change is being made before 9.0 actually goes final. There is already a statement that the default will change in a future release, and that seems sufficient to me. 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] SHOW TABLES
On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. Sincerely, Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent 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 Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. (O.k. I will take Euros too). JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent 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
Thom Brown wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php This is not at all what Simon proposed. He wants to make it a backend command, not a psql command. I don't have a horse in the race, particularly. If we really want more utility commands, my preference would be to concentrate on those that are hard rather than those that could be easily done, e.g. a command that would give you the SQL necessary to create a given object. 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] cvs to git migration - keywords
Hi, On 07/07/2010 08:31 PM, Andrew Dunstan wrote: Personally I favor leaving the expanded keywords in what we import, so that there's an exact mapping between what's in the final CVS repo and what's in the inital git repo, and then removing them entirely. I don't see that having old keyword expansions in the historical changesets is a bid deal. Nobody is going to base patches on them (I hope). Sorry for being somewhat late on this discussion. Another reason keeping the expanded keywords in historic revisions that hasn't been raised so far is, that they can easily be un-expanded with a script. But it's a lot harder to do the expansion, once you are on git, if you once happen to need that info. Of course, I'd also remove the keywords from every (active?) branch as a first commit after the import. I'd even favor removing those lines completely, just as sort of a cleanup commit. And no, that shouldn't pose any problem with outstanding patches, except you are fiddling with the tag itself. In which case you deserve to get a conflict. ;-) Regards Markus Wanner -- Sent 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 15/07/2010 17:48, Joshua D. Drake a écrit : On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) -- Guillaume http://www.postgresql.fr http://dalibo.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
On 15 July 2010 16:52, Andrew Dunstan and...@dunslane.net wrote: Thom Brown wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php This is not at all what Simon proposed. He wants to make it a backend command, not a psql command. My bad. But I find the following slightly odd: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. But I agree with the principal of improving usability. There's the issue of schema with SHOW TABLES though. It would either have to show tables and their associated schema in separate columns, or have an extended SHOW TABLES IN [SCHEMA] my_schema syntax. I personally think LIST object type makes more sense, although I guess the point is that SHOW would be familiar to MySQL defectors ;) Thom -- Sent 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 Thu, 2010-07-15 at 18:02 +0200, Guillaume Lelarge wrote: I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) Well I hadn't thought it out fully. I was just shutting down somebody elses idea that the feature had no legs. Which is obviously, not true. Let the discussion bloom :D JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent 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
As a common user -- probably a bit more than that now -- I'd have to say my reaction to '\d' instead of 'SHOW DATABASES;' was more of a meh moment for me. Furthermore, '\d' is much quick to type than 'SHOW DATABASES;', and much less likely to suffer typos. As for '\d' not being memorable: It sure as heck is! I think the real problem here is that there's a little effort required in learning a new set of commands when switching from a competing database. 'SHOW . . . .' cannot be implemented in psql alone. It would have to supported in the backend. So that other drivers are able to understand it as well. If it implemented in psql only, we will be bombarded with I don't understand this! 'SHOW . . . .' works when I do it at the command line, but not in my script! WTF?' The best solution is to offer a hint to the user in psql when they submit 'SHOW . . . .' with a response like: SHOW . . . . is not a valid command. Perhaps you mean \d . . . . Sincerely, Aaron On Thursday 15 July 2010 11:48:39 Joshua D. Drake wrote: On Thu, 2010-07-15 at 16:20 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. I have to agree with Simon here. \d is ridiculous for the common user. SHOW TABLES, SHOW COLUMNS makes a lot of sense. Just has something like DESCRIBE TABLE foo makes a lot more sense than \d. Sincerely, Joshua D. Drake -- Sent 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 Thu, 15 Jul 2010, Thom Brown wrote: If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent 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 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote: On Thu, 15 Jul 2010, Thom Brown wrote: If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Isn't that what the information_schema catalog is for? Thom -- Sent 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 Jul 15, 2010, at 10:50 AM, Joshua D. Drake j...@commandprompt.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. Damn straight. I like \d as well as anyone but there are real problems with it. Perhaps when we add \dxrvbfqS$: we'll stop to reflect on what they are. Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 15 Jul 2010, Thom Brown wrote:
On 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote:
On Thu, 15 Jul 2010, Thom Brown wrote:
If it's only a psql problem, why implement it as SQL? Is it just so we're
not adding keywords specifically to psql? In that case, it shouldn't
support QUIT.
Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend without
having to remember all the various system tables I need to query / join to
get the same results as \d gives me in psql ... same for any interface
language, really ...
Isn't that what the information_schema catalog is for?
I'd rather write:
SHOW TABLES;
then:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...
Marc G. FournierHub.Org Hosting Solutions S.A.
scra...@hub.org http://www.hub.org
Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org
--
Sent 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 15 July 2010 17:16, Marc G. Fournier scra...@hub.org wrote:
On Thu, 15 Jul 2010, Thom Brown wrote:
On 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote:
On Thu, 15 Jul 2010, Thom Brown wrote:
If it's only a psql problem, why implement it as SQL? Is it just so
we're
not adding keywords specifically to psql? In that case, it shouldn't
support QUIT.
Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend
without
having to remember all the various system tables I need to query / join
to
get the same results as \d gives me in psql ... same for any interface
language, really ...
Isn't that what the information_schema catalog is for?
I'd rather write:
SHOW TABLES;
then:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...
Yes, I see what you mean now. That would simplify things greatly.
Thom
--
Sent 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 Thu, Jul 15, 2010 at 05:38:35PM +0200, Magnus Hagander wrote: On Thu, Jul 15, 2010 at 17:30, Thom Brown thombr...@gmail.com wrote: On 15 July 2010 16:20, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 11:05 -0400, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: The biggest turn off that most people experience when using PostgreSQL is that psql does not support memorable commands. I would like to implement the following commands as SQL, allowing them to be used from any interface. SHOW TABLES SHOW COLUMNS SHOW DATABASES This has been discussed before, and rejected before. Please see archives. Many years ago. I think it's worth revisiting now in light of the number of people now joining the PostgreSQL community and the greater prevalence other ways of doing it. The world has changed, we have not. I'm not proposing any change in function, just a simpler syntax to allow the above information to be available, for newbies. Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. -- Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? In a word, YES! In two words, HELL, YES! Cheers, David. -- David Fetter da...@fetter.org 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] SHOW TABLES
On Thu, Jul 15, 2010 at 08:50:31AM -0700, Joshua D. Drake wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Looks like the last time this was discussed, there wasn't any clear conclusion. Someone created a patch and it's still on the TODO list: http://archives.postgresql.org/pgsql-hackers/2010-01/msg01845.php That one is about: a) doing it in psql., not the backend b) not actually implementing the command, but implementing hints for the user telling them which is the correct command Is there an actual common use-case for having these commands available for *non-psql* interfaces? Yes. We should provide a single, well described grammar for interacting with objects in the database regardless of client. I should be able to open ANY SQL terminal, and type SHOW ME THE MONEY and have Benjamins fall out. It's all about the Washingtons ;) Cheers, David. -- David Fetter da...@fetter.org 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] SHOW TABLES
On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. -- 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
On Jul 15, 2010, at 6:20 PM, Thom Brown wrote:
On 15 July 2010 17:16, Marc G. Fournier scra...@hub.org wrote:
On Thu, 15 Jul 2010, Thom Brown wrote:
On 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote:
On Thu, 15 Jul 2010, Thom Brown wrote:
If it's only a psql problem, why implement it as SQL? Is it just so
we're
not adding keywords specifically to psql? In that case, it shouldn't
support QUIT.
Personally, I think this is somethign that should go into the backend ...
I'd like to be able to write perl scripts that talk to the backend
without
having to remember all the various system tables I need to query / join
to
get the same results as \d gives me in psql ... same for any interface
language, really ...
Isn't that what the information_schema catalog is for?
I'd rather write:
SHOW TABLES;
then:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...
Yes, I see what you mean now. That would simplify things greatly.
Thom
exactly ...
and also: how many people outside the inner circle do you know who have ever
seen the information schema?
i have been in postgres business for more than 10 years (full time) and i
cannot name 5 customers who ever used the information schema to do show
tables ...
a big argument is: show tables (or whatever) could work for all versions to
come while a direct hit on the pg_class or so would not give you total
portability forever.
and yes, it is all about simplicity ...
it would not even add too much code to the backend and thus the complexity of
this feature can really be neglected from a maintenance point of view.
regards,
hans
--
Cybertec Schönig Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
--
Sent 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 Thu, Jul 15, 2010 at 18:35, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? -- 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] SHOW TABLES
On Thu, 2010-07-15 at 13:16 -0300, Marc G. Fournier wrote:
I'd rather write:
SHOW TABLES;
then:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...
+1
--
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
On Thu, 2010-07-15 at 11:10 -0500, Robert Haas wrote: Damn straight. I like \d as well as anyone but there are real problems with it. Perhaps when we add \dxrvbfqS$: we'll stop to reflect on what they are. Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration. It's OK, I wasn't asking you or anyone else to do this. -- 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
Magnus Hagander mag...@hagander.net wrote: The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? The solution to this on some products (e.g., Sybase ASE) is to embed such logic in stored procedures. A stored procedure can generate an intermingled stream of results sets with different layouts and INFO, WARN, etc. lines. If we *had* stored procedures with such capabilities, I think that would be the direction to go; since we don't, I'm ambivalent. I don't suppose a stored procedure implementation is in the works anywhere? -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
Simon Riggs si...@2ndquadrant.com wrote: Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration. It's OK, I wasn't asking you or anyone else to do this. I don't think a mess is OK regardless of who makes it. I think it would be wise to try to get some sort of consensus on what it would look like, rough as that process is. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 2010-07-15 18:07, Marc G. Fournier wrote: On Thu, 15 Jul 2010, Thom Brown wrote: If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Moving it into the backend (together with the other commands) would also solve this usabillity issue: WARNING: You are connected to a server with major version 8.4, but your psql client is major version 8.3. Some backslash commands, such as \d, might not work properly. testdb \d testtable ERROR: column reltriggers does not exist LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr... -- ^ Jesper
Re: [HACKERS] reducing NUMERIC size for 9.1
On 10 July 2010 00:58, Robert Haas robertmh...@gmail.com wrote: EnterpriseDB asked me to develop the attached patch to reduce the on-disk size of numeric and to submit it for inclusion in PG 9.1. After searching the archives, I found a possible design for this by Tom Lane based on an earlier proposal by Simon Riggs. Hi Robert, I'm reviewing this patch for the commitfest, and so far everything in the patch looks good. Compile and regression tests worked fine. However, I was trying to find a simple way to verify that it really was reducing the on-disk size of compact numeric values and didn't get the results I was expecting. I dropped one thousand numerics with value zero into a table and checked the on-disk size of the relation with your patch and on a stock 8.4 instance. In both cases the result was exactly the same. Shouldn't the table be smaller with your patch? Or is there something wrong with my test? CREATE TEMP TABLE numeric_short (a numeric); INSERT INTO numeric_short (a) SELECT 0::numeric FROM generate_series(1, 1000) i; Regards, BJ -- Sent 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 Thu, Jul 15, 2010 at 18:59, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote: On Thu, Jul 15, 2010 at 18:35, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? I imagined that we would do something similar to EXPLAIN, a set of text rows returned. Wouldn't that be useless for the case when an app wants to use it? An app will require it to be structured somehow. There's a reason we made EXPLAIN output data structured now. But I guess you could define an XML/JSON schema and return it in that... -- 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] SHOW TABLES
On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote: On Thu, Jul 15, 2010 at 18:35, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? I imagined that we would do something similar to EXPLAIN, a set of text rows returned. It should be possible to migrate \d options to using new outputs, when everything works in a useful manner. Probably not in this release. If I get some working solutions ready for Sept 15 we then have 4 months for other people to patch away at this. -- 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] Per-column collation, proof of concept
On Thu, Jul 15, 2010 at 4:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: The problem with not doing that is it breaks hashing --- hash joins and hash aggregation being the real pain points. citext works around this in a rather klugy fashion by decreeing that two strings are equal iff their str_tolower() conversions are bitwise equal. So it can hash the str_tolower() representation. But that's kinda slow and it fails in the general case anyhow, I think. I think the general equivalent would be to call strxfrm and hash the result of that. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs to git migration - keywords
On 7/7/10, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So what happens right now using the existing git repository is that the $PostgeSQL$ tags are there, but they're unexpanded. They just say $PostgreSQL$ rather than $PostgreSQL: tgl blah blah$. Really? All of them? Seems like that would have taken some intentional processing somewhere. AFAIK that's what CVS actually keeps in repo, it expands keywords when writing files out. If we could make the conversion work like that (rather than removing the whole line) it would negate my line-number-change argument, which might mean that files pulled from the repository would be close enough to their actual historical form that no one would mind. It's still a judgment call though. On balance I think I'd rather adopt the simple rule that historical file states in the git repository should match what you would have gotten from the cvs repository. I would prefer that the diffs should match what CVS gives / what got committed. Sanity-checking by comparing CVS checkout with GIT checkout with unexpanded keywords can be scripted easily enough, and is one-time affair. But humans want to review old diffs quite more frequently... +1 keeping keywords, but unexpanded. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 11:55 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration. It's OK, I wasn't asking you or anyone else to do this. I don't think a mess is OK regardless of who makes it. I think it would be wise to try to get some sort of consensus on what it would look like, rough as that process is. So starting a discussion thread is the wrong way to achieve that? How would you have me gain consensus if not this way? -- 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
On Thu, 2010-07-15 at 18:16 +0100, Simon Riggs wrote: On Thu, 2010-07-15 at 11:55 -0500, Kevin Grittner wrote: Simon Riggs si...@2ndquadrant.com wrote: Having said that, I want to urge that we spend a suitable amount of time and thought and care designing this, lest it turn into a mess. I have no interest in slamming something through without adequate consideration. It's OK, I wasn't asking you or anyone else to do this. I don't think a mess is OK regardless of who makes it. I think it would be wise to try to get some sort of consensus on what it would look like, rough as that process is. So starting a discussion thread is the wrong way to achieve that? How would you have me gain consensus if not this way? I think you guys are talking past each other. I believe Kevin was in fact stating that we needed to continue discussion. Joshua D. Drake -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent 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 Thu, 2010-07-15 at 19:02 +0200, Magnus Hagander wrote: I imagined that we would do something similar to EXPLAIN, a set of text rows returned. Wouldn't that be useless for the case when an app wants to use it? An app will require it to be structured somehow. There's a reason we made EXPLAIN output data structured now. But I guess you could define an XML/JSON schema and return it in that... The proposed goal is simplicity, not to be all things to all men. Anybody that wants structured output can i) write that as a future patch ii) write SQL to retrieve exactly what they want (preferred) -- 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
On Thu, 15 Jul 2010 17:09:32 +0100 Thom Brown wrote: On 15 July 2010 17:07, Marc G. Fournier scra...@hub.org wrote: On Thu, 15 Jul 2010, Thom Brown wrote: If it's only a psql problem, why implement it as SQL? Is it just so we're not adding keywords specifically to psql? In that case, it shouldn't support QUIT. Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Isn't that what the information_schema catalog is for? Is there a way to query all databases from information_schema? Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- Sent 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
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 The solution to this on some products (e.g., Sybase ASE) is to embed such logic in stored procedures. ...(skip other ideas)... I don't suppose a stored procedure implementation is in the works anywhere? Certainly some set-returning functions would be easy to implement, and could even be bolted on to existing systems for testing, etc. Now that plpgsql is installed by default, this is not the show-stopper it once was - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201007151321 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkw/Q+gACgkQvJuQZxSWSsiLpgCfU7Zt3ZmJwK0PrzYr5T0y6blD IiwAoNGoPXvxDhCbHn0MNKwxwh49fcdY =kfX8 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs to git migration - keywords
Marko Kreen wrote: On 7/7/10, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So what happens right now using the existing git repository is that the $PostgeSQL$ tags are there, but they're unexpanded. They just say $PostgreSQL$ rather than $PostgreSQL: tgl blah blah$. Really? All of them? Seems like that would have taken some intentional processing somewhere. AFAIK that's what CVS actually keeps in repo, it expands keywords when writing files out. No. It stores the expanded keyword. Just look in the ,v files in a CVS mirror and you'll see them. 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] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Moving it into the backend (together with the other commands) would also solve this usabillity issue: ... testdb \d testtable ERROR: column reltriggers does not exist LINE 1: SELECT relhasindex, relkind, relchecks, reltriggers, relhasr... This has already been solved at the psql level in recent versions of psql. Although, having a common functionality was always one of the proposed solutions to the problem, rather than having all the code paths inside of psql. - -- Greg Sabino Mullane g...@turnstep.com PGP Key: 0x14964AC8 201007151325 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkw/RMkACgkQvJuQZxSWSsglegCfU0qWorYc3c0Nq9+2weDu6dPi 3lgAn0r5w2Xbvbb3x57bjzC/LKzCe3em =Ie8l -END PGP SIGNATURE- -- Sent 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
Joshua D. Drake j...@commandprompt.com wrote: I think you guys are talking past each other. So it would appear. I was replying to a comment by Simon which sounded to me as though he didn't feel any further discussion was needed. I believe Kevin was in fact stating that we needed to continue discussion. Right, and judging from Simon's reply, we have no disagreement on that. Sorry for the confusion. I still think that the ability to issue one request and get back a series of responses, each of which could be the result of RAISE or a SELECT, would be valuable, and would be the best way to implement this; but of course it would be totally insane to try to burden Simon's proposal with such a requirement. I was hoping that someone had something in the works, close to fruition, which could be set as a prerequisite for Simon's feature -- so that it could be done in the best possible manner. The current alternatives of a separate request for each related bit of information versus a bunch of text lines makes me a bit queasy. Formated text (XML, YAML, etc.) seems worse than either of the above. -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] reducing NUMERIC size for 9.1
On Jul 15, 2010, at 11:58 AM, Brendan Jurd dire...@gmail.com wrote: On 10 July 2010 00:58, Robert Haas robertmh...@gmail.com wrote: EnterpriseDB asked me to develop the attached patch to reduce the on-disk size of numeric and to submit it for inclusion in PG 9.1. After searching the archives, I found a possible design for this by Tom Lane based on an earlier proposal by Simon Riggs. Hi Robert, I'm reviewing this patch for the commitfest, and so far everything in the patch looks good. Compile and regression tests worked fine. However, I was trying to find a simple way to verify that it really was reducing the on-disk size of compact numeric values and didn't get the results I was expecting. I dropped one thousand numerics with value zero into a table and checked the on-disk size of the relation with your patch and on a stock 8.4 instance. In both cases the result was exactly the same. Shouldn't the table be smaller with your patch? Or is there something wrong with my test? CREATE TEMP TABLE numeric_short (a numeric); INSERT INTO numeric_short (a) SELECT 0::numeric FROM generate_series(1, 1000) i; Well, on that test, you'll save only 2000 bytes, which is less than a full block, so there's no guarantee the difference would be noticeable at the relation level. Scale it up by a factor of 10 and the difference should be measurable. You might also look at testing with pg_column_size(). ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs to git migration - keywords
On 7/15/10, Andrew Dunstan and...@dunslane.net wrote: Marko Kreen wrote: On 7/7/10, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So what happens right now using the existing git repository is that the $PostgeSQL$ tags are there, but they're unexpanded. They just say $PostgreSQL$ rather than $PostgreSQL: tgl blah blah$. Really? All of them? Seems like that would have taken some intentional processing somewhere. AFAIK that's what CVS actually keeps in repo, it expands keywords when writing files out. No. It stores the expanded keyword. Just look in the ,v files in a CVS mirror and you'll see them. Eh. I stand corrected - what it actually does is even more bizarre - it stores whatever is on the disk, but then expands on re-write. So: - r1.1 contains $Id$ in the repo. - r1.2 contains $Id: 1.1$ in the repo. and so on... -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 15 Jul 2010, Magnus Hagander wrote: On Thu, Jul 15, 2010 at 18:35, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? One is an interface comamnd (ie. psql specific), the other is a generic command for any interface ... \d doesn't work in perl or tcl or ... so, for those, we're talking about adding a 'short form' (show tables), but if someone wants to use the long form of querying multiple table s(or information_schema), that option is still open to them ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs to git migration - keywords
* Marko Kreen mark...@gmail.com [100715 13:49]: Eh. I stand corrected - what it actually does is even more bizarre - it stores whatever is on the disk, but then expands on re-write. So: - r1.1 contains $Id$ in the repo. - r1.2 contains $Id: 1.1$ in the repo. and so on... It's actually slightly *worse* than that... The repository r$N contains what was in the commiters $N-1 *checked out* copy when he commits. So what's in the ,v file has *nothing* to do with reality, except by chance it's $n-1 because that's what was last checkout/updated most of the time.. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 12:36 -0500, Kevin Grittner wrote: I still think that the ability to issue one request and get back a series of responses, each of which could be the result of RAISE or a SELECT, would be valuable, and would be the best way to implement this; but of course it would be totally insane to try to burden Simon's proposal with such a requirement. I was hoping that someone had something in the works, close to fruition, which could be set as a prerequisite for Simon's feature -- so that it could be done in the best possible manner. The current alternatives of a separate request for each related bit of information versus a bunch of text lines makes me a bit queasy. Formated text (XML, YAML, etc.) seems worse than either of the above. Yes, the feature is harder than it first appears, but that's a reasonable reason for me to do it. But once we have the structures in place, rattling out a few patches should be easy enough. The bulk of the code could be very similar to psql and text EXPLAIN. So I would work on SHOW TABLES first, developing the core facilities required. Will come back with a full plan for discussion probably a month from now. -- 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
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Personally, I think this is somethign that should go into the backend ... I'd like to be able to write perl scripts that talk to the backend without having to remember all the various system tables I need to query / join to get the same results as \d gives me in psql ... same for any interface language, really ... Perl, eh? Most (all?) interfaces already have their own methods, e.g. $dbh-tables(); Of course, they don't really provide all the information that \d does, but you shouldn't need to be querying the system catalogs directly for *any* interface, including psql (for most common tasks). - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007151357 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkw/TBkACgkQvJuQZxSWSshuvACgtVpCav5qcl3nYsrsRdZ0vcT7 siUAoJaKkQ/RMAHcKCKJEyecjeEUhiQz =2mS9 -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cvs to git migration - keywords
* Aidan Van Dyk ai...@highrise.ca [100715 13:56]: * Marko Kreen mark...@gmail.com [100715 13:49]: Eh. I stand corrected - what it actually does is even more bizarre - it stores whatever is on the disk, but then expands on re-write. So: - r1.1 contains $Id$ in the repo. - r1.2 contains $Id: 1.1$ in the repo. and so on... It's actually slightly *worse* than that... The repository r$N contains what was in the commiters $N-1 *checked out* copy when he commits. So what's in the ,v file has *nothing* to do with reality, except by chance it's $n-1 because that's what was last checkout/updated most of the time.. And as a demo of what you can see in a project where some of my machines have -kk in .cvsrc, and others don't: [ai...@d1 faxd]$ grep '\$Id' faxQueueApp.c++,v |less /* $Id$ */ /* $Id: faxQueueApp.c++,v 1.115 2007/09/17 19:34:41 aidan Exp $ */ /* $Id$ */ /* $Id: faxQueueApp.c++,v 1.112 2007/07/23 21:04:09 aidan Exp $ */ /* $Id$ */ /* $Id: faxQueueApp.c++,v 1.113.2.2 2007/09/07 18:39:26 aidan Exp $ /* $Id$ */ /* $Id$ */ /* $Id: faxQueueApp.c++,v 1.111 2007/06/05 18:51:16 aidan Exp $ */ /* $Id$ */ a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] SHOW TABLES
On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. Well, if you put information_schema in the default path, it'd be SELECT * FROM TABLES; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bg worker: overview
Hi, On 07/15/2010 03:45 PM, Dimitri Fontaine wrote: We've been talking about this topic on -performance: Thank for pointing out this discussion, I'm not following -performance too closely. So, do you think we could use your work as a base for allowing custom daemon code? Daemon code? That sounds like it could be an addition to the coordinator, which I'm somewhat hesitant to extend, as it's a pretty critical process (especially for Postgres-R). With the step3, which adds support for sockets, you can use the coordinator to listen on pretty much any kind of socket you want. That might be helpful in some cases (just as it is required for connecting to the GCS). However, note that the coordinator is designed to be just a message passing or routing process, which should not do any kind of time consuming processing. It must *coordinate* things (well, jobs) and react promptly. Nothing else. On the other side, the background workers have a connection to exactly one database. They are supposed to do work on that database. I guess we need to think about how to separate external code and internal code, so a second layer could be necessary here. The background workers can easily load external libraries - just as a normal backend can with LOAD. That would also provide better encapsulation (i.e. an error would only tear down that backend, not the coordinator). You'd certainly have to communicate between the coordinator and the background worker. I'm not sure how match that fits your use case. The thread on -performance is talking quite a bit about connection pooling. The only way I can imagine some sort of connection pooling to be implemented on top of bgworkers would be to let the coordinator listen on an additional port and pass on all requests to the bgworkers as jobs (using imessages). And of course send back the responses to the client. I'm not sure how that overhead compares to using pgpool or pgbouncer. Those are also separate processes through which all of your data must flow. They use plain system sockets, imessages use signals and shared memory. I don't know enough about the pgagent or PgQ use cases to comment, sorry. Hope that's helpful, anyway. Regards Markus -- Sent 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
Excerpts from Peter Eisentraut's message of jue jul 15 14:21:26 -0400 2010: On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. Well, if you put information_schema in the default path, it'd be SELECT * FROM TABLES; Or even TABLE TABLES; weird though that is ... -- Sent 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 Jul 15, 2010, at 11:59 AM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 18:43 +0200, Magnus Hagander wrote: On Thu, Jul 15, 2010 at 18:35, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2010-07-15 at 17:38 +0200, Magnus Hagander wrote: Is there an actual common use-case for having these commands available for *non-psql* interfaces? There are many interfaces out there and people writing new ones everyday. We just wrote an interface for Android, for example. It is arguably *more* important to do this from non-psql interfaces. There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? I imagined that we would do something similar to EXPLAIN, a set of text rows returned. That seems rather wretched for machine-parsability, which I think is an important property for anything we do in this area. We need to think harder about how we could structure this to allow returning more than just a tabular result set while still allowing clients easy programmatic access to the underlying data. It should be possible to migrate \d options to using new outputs, when everything works in a useful manner. Probably not in this release. If I get some working solutions ready for Sept 15 we then have 4 months for other people to patch away at this. Sounds good, but we need agreement on a more detailed design first. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Well, if you put information_schema in the default path, it'd be SELECT * FROM TABLES; Except it also shows views[1]. Oh, and it has a bunch of other arcane and unwanted columns. Which we can't remove, nor can we add additional columns to it, because it was specified by the standard, which means that it was designed by committee and thus ugly and unusable for most things. [1] Granted, it would at least *tell* you its a view, unlike MySQL's SHOW TABLES. On the other hand, no owner as per \dt P.S. What's with the uppercase mania in this thread? Can we please get back to saying select * from tables and show tables? :) - -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkw/WA0ACgkQvJuQZxSWSsilEwCgqo8OefwS2B65JnJjH9xLVhp0 R2wAoPtuMxSPvVKfZ19yBDo8as59p7lv =YmAb -END PGP SIGNATURE- -- Sent 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 tor, 2010-07-15 at 19:21 +0200, Andreas 'ads' Scherbaum wrote: Is there a way to query all databases from information_schema? No. -- Sent 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 Thu, 2010-07-15 at 18:48 +, Greg Sabino Mullane wrote: P.S. What's with the uppercase mania in this thread? Can we please get back to saying select * from tables and show tables? :) The standard specifies that it it should be uppercase. :P JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering -- Sent 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 Thu, 15 Jul 2010, Peter Eisentraut wrote: On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. Well, if you put information_schema in the default path, it'd be SELECT * FROM TABLES; mre like: SELECT * FROM TABLES WHERE not a system table or information schema table; if we want to get *somewhere* close to \d ... Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent 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 15. Juli 2010 18:02:10 +0200 Guillaume Lelarge guilla...@lelarge.info wrote: And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) And as soon as you have this, people want to have that list ordered by size, schema or number of estimated tuples. I think what we really need to do is to improve our current interfaces and/or documentation to show how to do many tasks. In former courses i gave i always heard how easy it is in MySQL to get dictionary information, but once you are going to show the entry keys in how to query information_schema or using the administration functions in PostgreSQL the big aha begin to start. Maybe we can wrap many of the current psql output into SRF's to ease the retrieval for such kind of information other procedures or interfaces. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 13:16 -0300, Marc G. Fournier wrote:
Isn't that what the information_schema catalog is for?
I'd rather write:
SHOW TABLES;
then:
SELECT table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
AND table_schema NOT IN
('pg_catalog', 'information_schema');
And, the latter, unless I'm doing it regularly, is alot harder to remember
then the former ...
Thank you Marc. That is an excellent description of the problem that is
being ignored. We are no longer the academia database. We need to think
of real users here.
JD
--
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
--
Sent 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
Joshua D. Drake wrote: On Thu, 2010-07-15 at 18:48 +, Greg Sabino Mullane wrote: P.S. What's with the uppercase mania in this thread? Can we please get back to saying select * from tables and show tables? :) The standard specifies that it it should be uppercase. :P You're thinking about the unquoted identifiers. That's quite different from what is required of keywords. 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] SHOW TABLES
On 15/07/10 19:44, Robert Haas wrote: On Jul 15, 2010, at 11:59 AM, Simon Riggssi...@2ndquadrant.com wrote: I imagined that we would do something similar to EXPLAIN, a set of text rows returned. That seems rather wretched for machine-parsability, which I think is an important property for anything we do in this area. We need to think harder about how we could structure this to allow returning more than just a tabular result set while still allowing clients easy programmatic access to the underlying data. It should be possible to migrate \d options to using new outputs, when everything works in a useful manner. Probably not in this release. Feature sounds useful. I think our \dxx commands have grown a little unwieldy in the last version or two. Which is not to say you can take \d away :-) I was assuming the process would be something like: 1. Move existing \d queries into functions* 2. Convert psql to use those 3. Add SHOW xxx and have it return a single query Have it also issue NOTICE: from psql, try \dt for more info If/when we have multiple sets returned from one query it should be simple to provide something pretty close to \d... from a single command. Trying to format the data in the backend is probably just going to frustrate writers of different clients (of which I think we have quite a few now). * These functions could then be back-ported as an admin-pack too for clients/apps that wanted cross-version compatibility for these sorts of things. -- Richard Huxton Archonet Ltd -- Sent 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 Thu, 15 Jul 2010 22:01:34 +0300 Peter Eisentraut wrote: On tor, 2010-07-15 at 19:21 +0200, Andreas 'ads' Scherbaum wrote: Is there a way to query all databases from information_schema? No. This got rejected before, because of not in the standard. In this case: no way to answer SHOW DATABASES by just using information_schema. At least this question requires using the system tables. Bye -- Andreas 'ads' Scherbaum German PostgreSQL User Group European PostgreSQL User Group - Board of Directors Volunteer Regional Contact, Germany - PostgreSQL Project -- Sent 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 7/7/10 6:04 PM, Cédric Villemain wrote: I just faced production issue where it is impossible to alter table to adjust autovacuum settings in a pg8.4. (5K tps, 260M rows table, lock too much) We could try to resolve the COMMENT ON issue with the same mechanism. What we need is a table lock which blocks other DDL statements, but not DML. -- -- 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
[HACKERS] Listen/Notify in 9.0
Hi As I understand the changes to the notification system in 9.0, apart from being able to carry a payload, it will guarantee the order of delivery, and also it will keep the notification and notify any listener, even if the listener didn't register at the time of notification. Is this correct? What happens if the listener process dies, for how long time will it take up ressources? What happens if the listener process never picks up the notification, same question? What happens if the same process makes the same LISTEN request again. Will it take up more ressources? -- Med venlig hilsen Kaare Rasmussen, Jasonic Jasonic Telefon: +45 3816 2582 Nordre Fasanvej 12 2000 Frederiksberg Email: ka...@jasonic.dk -- Sent 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
Bernd Helmle wrote: --On 15. Juli 2010 18:02:10 +0200 Guillaume Lelarge guilla...@lelarge.info wrote: And would you add the complete syntax? I mean: SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern'] I'm wondering what one can do with the [FROM db_name] clause :) And as soon as you have this, people want to have that list ordered by size, schema or number of estimated tuples. I think what we really need to do is to improve our current interfaces and/or documentation to show how to do many tasks. In former courses i gave i always heard how easy it is in MySQL to get dictionary information, but once you are going to show the entry keys in how to query information_schema or using the administration functions in PostgreSQL the big aha begin to start. Maybe we can wrap many of the current psql output into SRF's to ease the retrieval for such kind of information other procedures or interfaces. I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? -- Bruce Momjian br...@momjian.ushttp://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] SHOW TABLES
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I was assuming the process would be something like: 1. Move existing \d queries into functions* 2. Convert psql to use those Oops! There's goes your ability to handle older versions of Postgres from the existing psql[1]. Cue angry mobs of DBAs with pitchforks. And no, a contrib like add-on won't go over well either. [1] (unless we keep both types of access around for many many years, until we can be sure that any database queries will have the new utility functions) - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201007151540 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkw/ZOIACgkQvJuQZxSWSshQ8gCfacG3r9N3rIn4Vbb/8tz0JK7S uCcAn037OYy9E5uiG84qBjjxzNox27+D =6sHa -END PGP SIGNATURE- -- Sent 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 15/07/10 19:06, Aaron W. Swenson wrote: The best solution is to offer a hint to the user in psql when they submit 'SHOW . . . .' with a response like: SHOW . . . . is not a valid command. Perhaps you mean \d . . . . +1. That doesn't force us to implement a whole new set of commands and syntax to describe stuff in the backend, duplicating the \d commands, but is polite to the users, and immediately guides them to the right commands. You could even do that in the backend for a few simple commands like SHOW TABLES: ERROR: syntax error at SHOW TABLES HINT: To list tables in the database, SELECT * FROM pg_tables or use the \d psql command. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen/Notify in 9.0
Kaare Rasmussen ka...@jasonic.dk writes: As I understand the changes to the notification system in 9.0, apart from being able to carry a payload, it will guarantee the order of delivery, and also it will keep the notification and notify any listener, even if the listener didn't register at the time of notification. Is this correct? Well, order of delivery depends on commit order, so there's only a pretty weak guarantee there, although I guess it is stronger than before. As for the other thing, listeners are *not* guaranteed to get any notices that were committed before they commit their LISTEN. They are guaranteed to get ones that were committed afterwards, but that's the same as before. 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] bg worker: overview
On Thu, Jul 15, 2010 at 1:28 PM, Markus Wanner mar...@bluegap.ch wrote: However, note that the coordinator is designed to be just a message passing or routing process, which should not do any kind of time consuming processing. It must *coordinate* things (well, jobs) and react promptly. Nothing else. so, merging this with the autovacuum will drop our hopes of having a time based autovacuum? not that i'm working on that nor i was thinking on working on that... just asking to know what the implications are, and what the future improves could be if we go this route -- Jaime Casanova www.2ndQuadrant.com Soporte y capacitación de PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
Bruce Momjian wrote: I assume SHOW TABLES would only be useful for interactive terminal sesssions, not for application code (which should use information_schema), so what non-psql interactive terminal programs are there? I think your assumption is questionable. Plenty of people use MySQL's SHOW TABLES in non-interactive settings (for good or ill). That's why any suggestion that we should return anything other than a resultset seems like a really terrible idea to me. This could presumably be implemented by creating a view to return the required information and then making SHOW TABLES an alias for select * from viewname. FYI, MS-SQL does this stuff with some stored procedures. I regularly use sp_columns to fiind out what I'm really being asked to interact with. See http://msdn.microsoft.com/en-us/library/ms182764.aspx 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] bg worker: overview
Hi, On 07/15/2010 09:51 PM, Jaime Casanova wrote: so, merging this with the autovacuum will drop our hopes of having a time based autovacuum? not that i'm working on that nor i was thinking on working on that... just asking to know what the implications are, and what the future improves could be if we go this route Not at all. Autovacuum should work exactly as before (seen from the outside, the implementation is a bit different). The coordinator is an async event processor. Events may origin from sockets, or may be driven by time, as is the case for autovacuum (up to something like a 1 second precision or something, I don't remember exactly). (There's nothing that needs to be merged with autovacuum. It already is merged, if you want). Regards Markus Wanner -- Sent 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 15. Juli 2010 15:52:24 -0400 Andrew Dunstan and...@dunslane.net wrote: FYI, MS-SQL does this stuff with some stored procedures. I regularly use sp_columns to fiind out what I'm really being asked to interact with. See http://msdn.microsoft.com/en-us/library/ms182764.aspx Yeah, something like this was in my mind. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On 15/07/10 20:43, Greg Sabino Mullane wrote: -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I was assuming the process would be something like: 1. Move existing \d queries into functions* 2. Convert psql to use those Oops! There's goes your ability to handle older versions of Postgres from the existing psql Arse. It's little details like this that demonstrate why I'm a user and not a hacker :-) -- Richard Huxton Archonet Ltd -- Sent 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 Jul 15, 2010, at 2:45 PM, Heikki Linnakangas wrote: On 15/07/10 19:06, Aaron W. Swenson wrote: The best solution is to offer a hint to the user in psql when they submit 'SHOW . . . .' with a response like: SHOW . . . . is not a valid command. Perhaps you mean \d . . . . +1. That doesn't force us to implement a whole new set of commands and syntax to describe stuff in the backend, duplicating the \d commands, but is polite to the users, and immediately guides them to the right commands. You could even do that in the backend for a few simple commands like SHOW TABLES: ERROR: syntax error at SHOW TABLES HINT: To list tables in the database, SELECT * FROM pg_tables or use the \d psql command. This sounds roughly like the patch I submitted in January (linked upthread), although that swiped the input before it hit the backend. I don't know if I like the idea of that HINT or not. 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] bg worker: overview
Excerpts from Jaime Casanova's message of jue jul 15 15:51:10 -0400 2010: On Thu, Jul 15, 2010 at 1:28 PM, Markus Wanner mar...@bluegap.ch wrote: However, note that the coordinator is designed to be just a message passing or routing process, which should not do any kind of time consuming processing. It must *coordinate* things (well, jobs) and react promptly. Nothing else. so, merging this with the autovacuum will drop our hopes of having a time based autovacuum? I don't think so, but I didn't know we had hopes for time-based autovacuum. What exactly do you mean? Initially there were some thoughts on schedule-based autovacuum parameters, but it seems that interest has dropped for that feature, so I haven't pushed much for that. However, I don't think that this patch series affects that in any way. BTW I think this patch series makes sense, though I haven't looked at it in detail. I guess it means I'll have to have a look at the IMessages stuff as well. -- Sent 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 Thu, Jul 15, 2010 at 04:20:12PM +0100, Simon Riggs wrote: Just for the record, I've never ever met anyone that said Oh, this \d syntax makes so much sense. I'm a real convert to Postgres now you've shown me this. The reaction is always the opposite one; always negative. Which detracts from our efforts elsewhere. Ah, that's true, we've never met in person ... Let me say that I recall finding the clean separation of what the client implements vs. what the server implements very useful when I was new to postgresql. Anything that doesn't start with a backslash works equally well from psql and from python/psycopg2, for example. If you make SHOW variants that are actually client side \d commands, you break that. Ross -- Ross Reedstrom, Ph.D. reeds...@rice.edu Systems Engineer Admin, Research Scientistphone: 713-348-6166 The Connexions Project http://cnx.orgfax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE -- Sent 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 Thu, 2010-07-15 at 13:44 -0500, Robert Haas wrote: Sounds good, but we need agreement on a more detailed design first. What do you mean? -- 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
On Jul 15, 2010, at 2:26 PM, Richard Huxton d...@archonet.com wrote: 3. Add SHOW xxx and have it return a single query Have it also issue NOTICE: from psql, try \dt for more info A big -1 from me on that. Going to a whole lot of trouble to implement something half as functional as what we have already sounds like a huge lose to me. If/when we have multiple sets returned from one query it should be simple to provide something pretty close to \d... from a single command. Sounds to me like this is just about a prerequisite for this project. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SHOW TABLES
On Thu, 2010-07-15 at 13:44 -0500, Robert Haas wrote: That seems rather wretched for machine-parsability, which I think is an important property for anything we do in this area. I completely disagree. This is for humans only, and mostly newbies only. Anybody that wants structured output can type the SQL and get as much structure as they want. I'm not reinventing the whole wheel. -- 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
On Thu, 2010-07-15 at 15:52 -0400, Andrew Dunstan wrote: This could presumably be implemented by creating a view to return the required information and then making SHOW TABLES an alias for select * from viewname. FYI, MS-SQL does this stuff with some stored procedures. I regularly use sp_columns to fiind out what I'm really being asked to interact with. See http://msdn.microsoft.com/en-us/library/ms182764.aspx Sounds good. OK, how about this: We write a function to derive the output, which can be executed as a function if people like that. We then make SHOW TABLEs a synonym for SELECT * FROM show_function() That way we get both in one go. -- 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 15 juil. 2010 à 18:43, Magnus Hagander mag...@hagander.net a écrit : The downside is that you are then limited to what can be returned as a resultset. A \d table in psql returns a hell of a lot more than that. So do we keep two separate formats for this? Or do we remove the current, useful, output format in favor of a much worse formt just to support more clients? I think we should keep both, and optionaly have a given psql \d command issue more than one SHOW query. Same as it does now with SELECT queries. That means we keep a resultset per SHOW query, so it's easy on the application. 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
[HACKERS] buildfarm owners, please add REL9_0_STABLE to your list of builds
Buildfarm owners: In case you have missed it, the source code tree has been branched somewhat earlier than has happened in previous release cycles, where the branch has occurred any time from just before release to several weeks after. That means that buildfarm owners need to add the new branch (REL9_0_STABLE) into their build mixes. As old branches are declared at End of Life (7.4 and 8.0 very soon, 8.1 later this year) the usefulness of doing regular builds on those will lessen and it would probably make sense to drop those from the build schedule. 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] SHOW TABLES
On Thu, Jul 15, 2010 at 02:31:10PM -0400, Alvaro Herrera wrote: Excerpts from Peter Eisentraut's message of jue jul 15 14:21:26 -0400 2010: On tor, 2010-07-15 at 17:35 +0100, Simon Riggs wrote: There should be one command to display a list of tables and it needs to be easily guessable for those who have forgotten. Well, if you put information_schema in the default path, it'd be SELECT * FROM TABLES; Or even TABLE TABLES; weird though that is ... Weird though that is, is *exactly* the problem we're trying to address here. SHOW TABLES is really, really easy to remember or guess. Cheers, David. -- David Fetter da...@fetter.org 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
[HACKERS] buildfarm housekeeping / planning
The buildfarm is now going on six years old (time flies when you're having fun!) and the database is now rather large - around 76Gb on disk. We'd like to reduce that quite a lot, especially by purging out the logs of old builds. And while the old data isn't publicly accessible, it has occasionally been used to run specialised queries to research particular issues. It's also arguably a useful historical resource that shouldn't be lightly abandoned. I'd like to get an idea of what the community regards as a reasonable amount of data to keep online and readily handy? Six months worth? A year? two years? Is it worth keeping logs of error stages longer than successful stages? If so, what should the periods be? One of the things that I'd like to be able to do is FTS on the logs. Part of our plan is to move to a much more modern version of Postgres. Keeping the logs to a reasonable size will possibly allow us to provide FTS, although I haven't discussed that part with Josh Drake yet, and as it's hosted at CMD he does get a say :-) 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] buildfarm housekeeping / planning
Andrew Dunstan and...@dunslane.net writes: The buildfarm is now going on six years old (time flies when you're having fun!) and the database is now rather large - around 76Gb on disk. We'd like to reduce that quite a lot, especially by purging out the logs of old builds. And while the old data isn't publicly accessible, it has occasionally been used to run specialised queries to research particular issues. It's also arguably a useful historical resource that shouldn't be lightly abandoned. As long as the historical data is kept somewhere, I agree that it doesn't need to be readily available on-line. 10GB a year is not a lot of data these days, so it seems like we ought to be able to archive it indefinitely; but I can see that keeping it available on the web might run into some money. (You could also argue that there's no need to archive more than say five years back, but I think that's a different discussion.) I'd like to get an idea of what the community regards as a reasonable amount of data to keep online and readily handy? Six months worth? A year? two years? Is it worth keeping logs of error stages longer than successful stages? If so, what should the periods be? Six months is probably plenty, really, especially if that means we can make the data more available than it is now. I'm not convinced that successful builds should be purged more quickly, as there's often reason to look for warnings, funny events in the postmaster log, etc. One of the things that I'd like to be able to do is FTS on the logs. +1. +10 even. I think this'd be a quantum jump in the usefulness of the log archives. I frequently wonder things like what other machines are showing this warning, and right now it's impractical to research 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] elimination of code duplication in DefineOpFamily()
Brent Dombrowski brent.dombrow...@gmail.com writes: [ review of KaiGai-san's patch ] Committed. Thanks for the patch, and the review. 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] gSoC - ADD MERGE COMMAND - code patch submission
Dear Hackers I considered my situation. And I found that I didn't communicate well with you, as makes you have little confidence on my project. Most of the time I just work by myself and not report to you frequently. I always want to finish a solid stage progress before do a submission. This may be a bad habit in the remote project. In fact, I have a detailed design on how to implement the command and I am working hard these days to catch the schedule. In my design, 1. the merge command is firstly transformed to a MergeStmt node in parser. And analyzer will generate a left out join query as the top query (or main query). This query is similar to a SELECT command query, but I set target relation in it. The top query will drive the scanning and joining over target and source tables. The merge actions are transformed into lower level queries. I create a Query node for each of them and append them in a newly create List field mergeActQry. The action queries have different command type and specific target list and qual list, according to their declaration by user. But they all share the same range table. This is because we don't need the action queries to be planned latter. The joining strategy is decided by the top query. We are only interest in their specific action qualifications. In other words, these action queries are only containers for their target list and qualifications. 2. When the query is ready, it will be send to rewriter. In this part, we can call RewriteQuery() to handle the action queries. The UPDATE action will trigger rules on UPDATE, and so on. What need to be noticed are: 1. the actions of the same type should not be rewritten repeatedly. If there are two UPDATE actions in merge command, we should not trigger the ON UPDATE rules twice. 2. if an action type is fully replaced by rules, we should remove all actions of this type from the action list. Rewriter will also do some process on the target list of each action. The first submission has finished the above part. 3. In planner, the top level query is handled in a normal way. Since it has almost the same structure as a SELECT query, the planner() function can work on it straight forward. However, we need a small change here. The merge command has a target relation, which need a ctid junk attribute in the target list. The ctid is required by the UPDATE and DELETE actions. Besides, for each of the action queries, we also need to create a Plan node. We don't need to do a full plan on the action queries. The crucial point is to preprocess the target list and qualification of each action. (Explanation for this point. The execution of a merge action is composed by two parts. The top plan will be executed in the main loop, and return the joined tuples one by one. And a action will apply its qualification on the returned tuples. If succeed, it will take the action and do corresponding modification on the target table. Thus, even we have a Plan node created for each action, we don't want to throw it directly into Planner() function. That will generate a new plan over the tables in Range Table, which is very probably different with the top-level plan. If we run the action plans directly, they will be confilict with each other). I create a function merge_action_planner() to do this job. This part is added at the end of standard_planner(). After that, all the plans of merge actions are linked into a new List filed in PlannedStmt result of the top plan. 4. When planner is finished, the plan will be send to executor through PortalRun(). As a new command, merge will chose the PORTAL_MULTI_QUERY strategy, and be sent to ProcessQuery() function. 5. As in the ExecutorStart() part, we need to set junkfilter for merge command, since we have a ctid junk attr in target list. And, the merge action plans should also be initialized and transformed into PlanState nodes. However, the initialization over action plan is only focus on the target list and quals. We don't need other part of traditional plan initialization, since these action plans are not for scanning or joining (this is the job of top plan). We only want to transform the action information into standard format that can be used by qualification evaluator in executor. I HAVE DONE ALL THE ABOVE IN A SECOND SUBMISSION. 6. In ExecutorRun() part, the top plan will be passed into ExecutePlan(). The action planstates can be found in the estate-es_plannedstmt field. The top plan can return tuples of the left out join on source table and target table. (I can see the tuple be returned in my codes). Thus, the design is correct. At least the top plan can do its work well. In the junkfilter, if we can find a non-null ctid, it is a matched tuple, or else, it is a NOT MATCHED tuple. Then we need to evaluate the additional quals of the actions one by one. If the evaluations of one action succeed, we will take this action and skip the remaining ones. Since the target list and qual expressions are all
Re: [HACKERS] SHOW TABLES
Excerpts from David Fetter's message of jue jul 15 19:19:47 -0400 2010: On Thu, Jul 15, 2010 at 02:31:10PM -0400, Alvaro Herrera wrote: Or even TABLE TABLES; weird though that is ... Weird though that is, is *exactly* the problem we're trying to address here. SHOW TABLES is really, really easy to remember or guess. Eh? I thought the problem being solved is that the command is implemented in the client side rather than the server side, so all interfaces need to implement it time and time again. With TABLE TABLES there's no such problem. TABLE has also the advantage (over SHOW) that it already works on 8.4, and moreover it is SQL standard. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
