[HACKERS] 9.4 Proposal: Initdb creates a single table
We start with a database called Postgres and a schema called Public. Yet we don't start up with any usable tables. I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. The idea is to have a stupidly obvious and easy table that will potentially be usable by just about everyone, in any language. If you don't like it, don't use it. If you really dislike it, drop it. But for new people coming to Postgres, they will have a data object to access and begin using the database immediately. Their code will work, their examples will work. OK, so they need to go back and think about the design, but at least they got it to work and will be encouraged to do more. Remember when we didn't have a database called Postgres? Remember how much simpler life is now? Remember that now. We can also insert a single row, Id = 0 with Postgres sample data in it, but that seems a step too far. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
Simon Riggs si...@2ndquadrant.com writes: By now, some of you will be doubled over laughing as if this is an April fool joke. Indeed. 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] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 02:11 PM, Simon Riggs wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); Without particular comment on the need for the table, I'd be concerned about calling it postgres. My personal impression from Stack Overflow etc has been that users are readily confused by the fact that we have: - Database engine/system postgres - backend binary postgres (they see it in ps) - unix user postgres - Pg superuser postgres - database postgres Sure, there's an argument for running with the theme here, but I suspect using the name postgres for a default table will just muddy the waters a bit more. Even postgres_table would help. It *absolutely must* be lower case, whatever it is, IMO. If you're going for newest-of-the-newbies, the last thing you want to do is having them dealing with it being just Postgres in some places, and having to be Postgres in others. Personally, don't know if I'm convinced it's overly worth doing - but I think it's silly to dismiss without actually corralling up some users who're unfamiliar with Pg and watching them get started. I'd love to see some properly conducted usability studies of Pg, and something like this would fit in well. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
Hello if you are thinking about this direction, then store there some demo project. I am don't think so isolated table has significant price. Regards Pavel 2014-04-23 8:45 GMT+02:00 Craig Ringer cr...@2ndquadrant.com: On 04/23/2014 02:11 PM, Simon Riggs wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); Without particular comment on the need for the table, I'd be concerned about calling it postgres. My personal impression from Stack Overflow etc has been that users are readily confused by the fact that we have: - Database engine/system postgres - backend binary postgres (they see it in ps) - unix user postgres - Pg superuser postgres - database postgres Sure, there's an argument for running with the theme here, but I suspect using the name postgres for a default table will just muddy the waters a bit more. Even postgres_table would help. It *absolutely must* be lower case, whatever it is, IMO. If you're going for newest-of-the-newbies, the last thing you want to do is having them dealing with it being just Postgres in some places, and having to be Postgres in others. Personally, don't know if I'm convinced it's overly worth doing - but I think it's silly to dismiss without actually corralling up some users who're unfamiliar with Pg and watching them get started. I'd love to see some properly conducted usability studies of Pg, and something like this would fit in well. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
Simon Riggs wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. The idea is to have a stupidly obvious and easy table that will potentially be usable by just about everyone, in any language. I am a PostgreSQL newbie. How is this table useful for me? I want to develop a database application. I want to store personal data like name and birth date! Actually, I feel confused. What should I do with this table? Is it part of the database system? Will the database be broken if I drop it? Do I have to ship it with my application? If you don't like it, don't use it. If you really dislike it, drop it. No, I'm not the kind of person who reads a manual to figure out what to do with this table. I want to start coding *right now*. But for new people coming to Postgres, they will have a data object to access and begin using the database immediately. Their code will work, their examples will work. OK, so they need to go back and think about the design, but at least they got it to work and will be encouraged to do more. I have found a sample application for personal data on the internet. How can I make it work with this table? Remember when we didn't have a database called Postgres? Remember how much simpler life is now? Remember that now. Good that you mention that! I have wondered what to do with it. When I first connected to PostgreSQL, I created a sample table, but the senior developer from the other office told me that this is the postgres database and that I shouldn't create any objects there. What is it good for? Can I delete it? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 03:20 PM, Albe Laurenz wrote: Good that you mention that! I have wondered what to do with it. When I first connected to PostgreSQL, I created a sample table, but the senior developer from the other office told me that this is the postgres database and that I shouldn't create any objects there. What is it good for? Can I delete it? A key difference between the postgres DB and a default table is that the postgres DB is very convenient with PostgreSQL's default of connecting to a DB of the same name as the user. We don't have a corresponding INSERT VALUES (42); SELECT fred; where there is some invisible implicit table name. Personally I wish Pg was able to start w/o connecting to any specific DB, but that's just not how the architecture works, and with that limitation the postgres DB seemed like a good compromise. (That said, it's really weird that the username of the superuser defaults to the current unix user when initdb'ing, but the db created by default is still always postgres). -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Minor improvements in alter_table.sgml
(2014/04/15 15:27), Etsuro Fujita wrote: (2014/04/14 23:53), Robert Haas wrote: On Fri, Apr 11, 2014 at 5:00 AM, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: Attached is an updated version of the patch. I think the other changes deserve to be considered separately, and in particular I'm still not sure it's a good idea to document both OF type_name and type_name. I've agreed on that point, but I think apart from the others, the trivial typo should be corrected. Patch attached (doc-altertable-typo.patch). I noticed the description of index_name should also be corrected, because it is currently used not only in CLUSTER ON, but in ADD table_constraint_using_index. (It will also be used in REPLICA IDENTITY in 9.4.) Patch attached (doc-altertable-indexname.patch). Thanks, Best regards, Etsuro Fujita diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 85705e9..42167d8 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -720,7 +720,7 @@ ALTER TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceable /varlistentry varlistentry - termreplaceable class=PARAMETERtype/replaceable/term + termreplaceable class=PARAMETERdata_type/replaceable/term listitem para Data type of the new column, or new data type for an existing diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 85705e9..0354dde 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -805,7 +805,7 @@ ALTER TABLE [ IF EXISTS ] replaceable class=PARAMETERname/replaceable termreplaceable class=PARAMETERindex_name/replaceable/term listitem para -The index name on which the table should be marked for clustering. +The name of an existing index. /para /listitem /varlistentry -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 23 April 2014 07:14, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: By now, some of you will be doubled over laughing as if this is an April fool joke. Indeed. I do like to share the odd joke now and then, it has to be said. So I'm glad I enriched your day. I was taught that ideas are accepted in this order: first we think them a joke, then we perceive them as a threat, then they become obvious. I can't find a Wikipedia article to give that idea more weight (OK, that was a joke). Just trying to think about how to improve the out of the box experience in ways that others already consider obvious. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
Craig Ringer wrote: Good that you mention that! I have wondered what to do with it. When I first connected to PostgreSQL, I created a sample table, but the senior developer from the other office told me that this is the postgres database and that I shouldn't create any objects there. What is it good for? Can I delete it? A key difference between the postgres DB and a default table is that the postgres DB is very convenient with PostgreSQL's default of connecting to a DB of the same name as the user. I did not seriously want to dispute the value of the postgres DB, I just think that making things easier to understand for the newbie is *not* its greatest merit. It is mostly for the convenience of the administrator, right? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] What use case is make_tuple_indirect() supposed to illustrate?
Hi, On 2014-04-22 20:22:23 -0400, Tom Lane wrote: And for that matter, it's a bit silly to be testing make_tuple_indirect in a BEFORE INSERT/UPDATE trigger, because even if the tuple gets out of the trigger without being flattened, it will certainly get flattened mere nanoseconds later before it gets written out to disk. (If it did not, the test case would fail altogether, since the indirect values in memory only survive for the length of the current transaction.) Well, that's part of what it's essentially testing. We better not end up with a indirect datum, pointing to memory after all, ending up in a disk tuple. So I'm wondering exactly what use-case this test is supposed to represent. Testing stuff I was concerned could break without tests. Especially as this was committed before the rest of the decoding stuff was. Or is the whole thing just a toy anyway? Because the more I look at that patch, the less it looks like it could do anything useful, short of adding a ton of infrastructure that's not there now. Indirect toast tuples are actively used in logical decoding. So there is a usecase. I think there's further potential uses for both the infrastructure for further toast types in general and specifically indirect toast tuples. But we'll see. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Missing pfree in logical_heap_rewrite_flush_mappings()
On 2014-04-22 22:37:37 -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: I had to revert this patch. It causes a failure in the /contrib/test_decoding regression test. On closer inspection, it was simply pfree'ing the wrong pointer. Thanks for fixing. I fixed that and also undid the allocation in a different memory context, which didn't seem to be a particularly good idea, unless you've got a specific reason why CurrentMemoryContext would be the wrong place for a transient allocation. That should be fine. I don't see any reason not to use palloc. logical_rewrite_log_mapping() has to allocate longer living memory, I guess it was copied from there. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
On 23/04/14 00:40, Peter Geoghegan wrote: On Tue, Apr 22, 2014 at 3:32 PM, Bruce Momjian br...@momjian.us wrote: Where are we on the default JSONB opclass change? FWIW, I still don't have any strong opinion here. I defer to others on this question. I vote for changing it, even though neither option is ideal I think that given the nature of datatype the current default will break inserts for common usage pattern and that's much worse than not being able to use the index for some operators. -- Petr Jelinek http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 6:11 PM, Simon Riggs si...@2ndquadrant.com wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. I'm not quite sure it would serve the same purpose as to what you're proposing here, but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html In the above link we have some table called empsalary, but the new user can't go an execute that query to test it without first working out how to first create a table and insert some data into that table. It would be really nice if new users could create this example database somehow and then they could play around with the example queries we put in the manual. Regards David Rowley
Re: [HACKERS] CREATE FOREIGN TABLE ( ... LIKE ... )
(2014/04/08 9:26), Michael Paquier wrote: On Tue, Apr 8, 2014 at 5:24 AM, Andres Freund and...@2ndquadrant.com wrote: On 2014-04-05 11:46:16 -0400, Tom Lane wrote: ISTM this is because the proposed feature is wrongheaded. The basic concept of CREATE TABLE LIKE is that you're copying properties from another object of the same type. You might or might not want every property, but there's no question of whether you *could* copy every property. In contrast, what this is proposing to do is copy properties from (what might be) a plain table to a foreign table, and those things aren't even remotely the same kind of object. It would make sense to me to restrict LIKE to copy from another foreign table, and then there would be a different set of INCLUDING/EXCLUDING options that would be relevant (options yes, indexes no, for example). I actually think it's quite useful to create a foreign table that's the same shape as a local table. And the patches approach of refusing to copy thinks that aren't supported sounds sane to me. This could be improved as well: it would be useful to be able to copy the column options of another foreign table. Yes, I think so, too. But to think of validating generic column/table options, I think we would probably need to restrict LIKE to copy from another foreign table maybe using the same FDW. So, I'd like to vote for Tom's idea. Thanks, Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Typo in doc/src/sgml/monitoring.sgml? s/tranche/trance?
Hi, Attached fixes a typo in doc/src/sgml/monitoring.sgml. -- Amit docs-monitoring-lwlock-tranche-not-trance-fix.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typo in doc/src/sgml/monitoring.sgml? s/tranche/trance?
On Wed, Apr 23, 2014 at 6:15 PM, Amit Langote amitlangot...@gmail.com wrote: Hi, Attached fixes a typo in doc/src/sgml/monitoring.sgml. Sorry, typo in subject (kinda ironic!); I meant s/trance/tranche? Patch attached again. -- Amit docs-monitoring-lwlock-tranche-not-trance-fix.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Runing DBT2 on Postgresql
Hi All, I am trying to install dbt2 on postgresql database. cmake(configure) command work fine and but make command(build) give an error given below. I have no idea about how to solve it I have set export LD_LIBRARY_PATH=home/abhi/project/lib:$LD_LIBRARY_PATH dbt2pgdata = /home/abhi/project/pgsql/DemoDir/ export PATH=$HOME/project/bin:$PATH CMakeFiles/bin/dbt2-client.dir/src/client.o: In function `startup': /home/abhi/project/dbt2/src/client.c:344: undefined reference to `pthread_create' CMakeFiles/bin/dbt2-client.dir/src/listener.o: In function `init_listener': /home/abhi/project/dbt2/src/listener.c:94: undefined reference to `pthread_attr_setstacksize' /home/abhi/project/dbt2/src/listener.c:99: undefined reference to `pthread_create' CMakeFiles/bin/dbt2-client.dir/src/db_threadpool.o: In function `db_threadpool_init': /home/abhi/project/dbt2/src/db_threadpool.c:226: undefined reference to `pthread_attr_setstacksize' /home/abhi/project/dbt2/src/db_threadpool.c:230: undefined reference to `pthread_create' CMakeFiles/bin/dbt2-client.dir/src/common.o: In function `get_think_time': /home/abhi/project/dbt2/src/common.c:149: undefined reference to `log' CMakeFiles/bin/dbt2-client.dir/src/libpq/dbc_common.o: In function `commit_transaction': /home/abhi/project/dbt2/src/libpq/dbc_common.c:25: undefined reference to `PQexec' /home/abhi/project/dbt2/src/libpq/dbc_common.c:26: undefined reference to `PQresultStatus' /home/abhi/project/dbt2/src/libpq/dbc_common.c:27: undefined reference to `PQerrorMessage' /home/abhi/project/dbt2/src/libpq/dbc_common.c:29: undefined reference to `PQclear' CMakeFiles/bin/dbt2-client.dir/src/libpq/dbc_common.o: In function `_connect_to_db': /home/abhi/project/dbt2/src/libpq/dbc_common.c:44: undefined reference to `PQconnectdb' /home/abhi/project/dbt2/src/libpq/dbc_common.c:45: undefined reference to `PQstatus' /home/abhi/project/dbt2/src/libpq/dbc_common.c:48: undefined reference to `PQerrorMessage' /home/abhi/project/dbt2/src/libpq/dbc_common.c:49: undefined reference to `PQfinish' CMakeFiles/bin/dbt2-client.dir/src/libpq/dbc_common.o: In function `_disconnect_from_db': /home/abhi/project/dbt2/src/libpq/dbc_common.c:58: undefined reference to `PQfinish' Please guide me through !! Regards, Rohit Goyal
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 08:11 AM, Simon Riggs wrote: We start with a database called Postgres and a schema called Public. Yet we don't start up with any usable tables. I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. With default access controls it still aint :( And most of complaints I have heard are about the need of fidgeting with pg_hba.conf to get *anything* started. So maybe we could start with something like this at the end of initdb: createdb example psql example sample# create table stuff(id serial primary key, data jsonb); sample# grant all on stuff to public; And also have the following lines in pg_hba.conf for it local all all trust hostall all 127.0.0.1/32trust hostall all ::1/128 trust This would solve most of the frustration with starting pg development for newcomers Cheers Hannu By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. The idea is to have a stupidly obvious and easy table that will potentially be usable by just about everyone, in any language. If you don't like it, don't use it. If you really dislike it, drop it. But for new people coming to Postgres, they will have a data object to access and begin using the database immediately. Their code will work, their examples will work. OK, so they need to go back and think about the design, but at least they got it to work and will be encouraged to do more. Remember when we didn't have a database called Postgres? Remember how much simpler life is now? Remember that now. We can also insert a single row, Id = 0 with Postgres sample data in it, but that seems a step too far. -- Hannu Krosing PostgreSQL Consultant Performance, Scalability and High Availability 2ndQuadrant Nordic OÜ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Typo in doc/src/sgml/monitoring.sgml? s/tranche/trance?
On 04/23/2014 12:15 PM, Amit Langote wrote: Hi, Attached fixes a typo in doc/src/sgml/monitoring.sgml. Thanks, applied. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
On Wed, Apr 23, 2014 at 10:20:42AM +0200, Petr Jelinek wrote: On 23/04/14 00:40, Peter Geoghegan wrote: On Tue, Apr 22, 2014 at 3:32 PM, Bruce Momjian br...@momjian.us wrote: Where are we on the default JSONB opclass change? FWIW, I still don't have any strong opinion here. I defer to others on this question. I vote for changing it, even though neither option is ideal I think that given the nature of datatype the current default will break inserts for common usage pattern and that's much worse than not being able to use the index for some operators. I agree. We should choose the most general option as the default. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 23 April 2014 09:26, David Rowley dgrowle...@gmail.com wrote: On Wed, Apr 23, 2014 at 6:11 PM, Simon Riggs si...@2ndquadrant.com wrote: I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. I'm not quite sure it would serve the same purpose as to what you're proposing here, but for a long time I've thought that it would be nice if PostgreSQL came with an example database that had a number of tables, perhaps that mock up some easy to relate to real-world application. These would be very useful to use as examples in the documents instead of inventing them in the ad-hoc way that we currently do. Like here: http://www.postgresql.org/docs/9.3/static/tutorial-window.html +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Since we don't have that now, it won't work for 9.4. I still like the idea of a database installed by default on initdb, by default. Packagers can of course do what they like. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
This seems like a much better idea - whereas a single table, related to nothing - on the other hand, is at best not very helpful (and it could be argued, might contribute to teaching poor data data design). Regards Mark On 23/04/14 19:13, Pavel Stehule wrote: Hello if you are thinking about this direction, then store there some demo project. I am don't think so isolated table has significant price. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
On Wed, Apr 23, 2014 at 7:56 PM, Bruce Momjian br...@momjian.us wrote: On Wed, Apr 23, 2014 at 10:20:42AM +0200, Petr Jelinek wrote: I vote for changing it, even though neither option is ideal I think that given the nature of datatype the current default will break inserts for common usage pattern and that's much worse than not being able to use the index for some operators. I agree. We should choose the most general option as the default. +1. Less operators are supported by the now-named jsonb_hash_ops but at least users won't be surprised by failures caused by too long index records. -- Michael
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
* Simon Riggs (si...@2ndquadrant.com) wrote: +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Agreed. I'll add it to the GSoC ideas page. Since we don't have that now, it won't work for 9.4. None of this is on the table for 9.4 as far as I'm concerned.. I still like the idea of a database installed by default on initdb, by default. Packagers can of course do what they like. I fail to see the point of adding something that's targetted at novice / end-users which 90% (yes, it's a random # that I pulled, but it's surely the majority, at least) of installs won't have. For my 2c, it'd also be a disservice to our users and to ourselves to encourage a design that minimizes the database's understanding of the data and greatly reduces the set of PG's capabilities that can be used. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] pg_upgrade and epoch
On Wed, Apr 23, 2014 at 07:08:42AM +0400, Sergey Burladyan wrote: On Wed, Apr 23, 2014 at 6:38 AM, Sergey Konoplev gray...@gmail.com wrote: BTW, I didn't manage to make a test case yet. Recently, when I was migrating several servers to skytools3 and upgrading from 9.0 to 9.2, I noticed that epoch was copied, timeline id was 0 after upgrade, but ... This is strange, if I not mistaken XID copied by copy_clog_xlog_xid(void): http://doxygen.postgresql.org/pg__upgrade_8c_source.html#l00398 and there is no epoch (-e XIDEPOCH) in pg_resetxlog call args ... 34359739064 switched to 756 after upgrade Yes, that looks about right, though not exact: 34359739064 - 8 * 2^32 = 696 I looked at this last night and am trying to figure out the extent of the bug. We have had timestamp epochs since pg_upgrade was created and this is the first time I am hearing that not preserving timestamp epochs is a problem. Do we store the timestamp epoch anywhere in the data files, or just in pg_controldata? (pg_upgrade does not preserve WAL files.) I know we have talked about using epochs on data pages but I am not sure we have ever done it. Sergey, are you seeing a problem only because you are interacting with other systems that didn't reset their epoch? It is an easy fix, but I need to understand the scope of the problem. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL replay bugs
On 04/17/2014 07:59 PM, Heikki Linnakangas wrote: On 04/08/2014 06:41 AM, Michael Paquier wrote: On Tue, Apr 8, 2014 at 3:16 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I've been playing with a little hack that records a before and after image of every page modification that is WAL-logged, and writes the images to a file along with the LSN of the corresponding WAL record. I set up a master-standby replication with that hack in place in both servers, and ran the regression suite. Then I compared the after images after every WAL record, as written on master, and as replayed by the standby. Assuming that adding some dedicated hooks in the core able to do actions before and after a page modification occur is not *that* costly (well I imagine that it is not acceptable in terms of performance), could it be possible to get that in the shape of a extension that could be used to test WAL record consistency? This may be an idea to think about... Yeah, working on it. It can live as a patch set if nothing else. This has been very fruitful, I just committed another fix for a bug I found with this earlier today. There are quite a few things that cause differences between master and standby. We have hint bits in many places, unused space that isn't zeroed etc. [a few more fixed bugs later] Ok, I'm now getting clean output when running the regression suite with this tool. And here is the tool itself. It consists of two parts: 1. Modifications to the backend to write the page images 2. A post-processing tool to compare the logged images between master and standby. The attached diff contains both parts. The postprocessing tool is in contrib/page_image_logging. See contrib/page_image_logging/README for instructions. Let me know if you have any questions or need further help running the tool. I've also pushed this to my git repository at git://git.postgresql.org/git/users/heikki/postgres.git, branch page_image_logging. I intend to keep it up-to-date with current master. This is a pretty ugly hack, so I'm not proposing to commit this in the current state. But perhaps this could be done more cleanly, by adding some hooks in the backend as Michael suggested. - Heikki diff --git a/contrib/page_image_logging/Makefile b/contrib/page_image_logging/Makefile new file mode 100644 index 000..9c68bbc --- /dev/null +++ b/contrib/page_image_logging/Makefile @@ -0,0 +1,20 @@ +# contrib/page_image_logging/Makefile + +PGFILEDESC = postprocess-images - + +PROGRAM = postprocess-images +OBJS = postprocess-images.o + +PG_CPPFLAGS = -I$(libpq_srcdir) +PG_LIBS = $(libpq_pgport) + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/postprocess-images +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/page_image_logging/README b/contrib/page_image_logging/README new file mode 100644 index 000..2c3d271 --- /dev/null +++ b/contrib/page_image_logging/README @@ -0,0 +1,50 @@ +Usage +- + +1. Apply the patch + +2. Set up a master and standby. + +3. stop master, then standby. + +4. Remove $PGDATA/buffer-images from both servers. + +5. Start master and standby + +6. Run make installcheck, or whatever you want to test + +7. Stop master, then standby + +8. compare the logged page images using the postprocessing tool: + +./postprocess-images ~/data-master/buffer-images ~/data-standby/buffer-images differences + +9. The 'differences' file should be empty. If not, investigate. + +Tips + + +The page images take up a lot of disk space! The PostgreSQL regression +suite generates about 11GB - double that when the same is generated also +in a standby. + +Always stop the master first, then standby. Otherwise, when you restart +the standby, it will start WAL replay from the previous checkpoint, and +log some page images already. Stopping the master creates a checkpoint +record, avoiding the problem. + +If you get errors like this from postprocess-images: + +could not reorder line XXX + +It can be caused by an all-zeros page being logged with XLOG HEAP_NEWPAGE +records. Look at the line in the buffer-image file, see if it's all-zeros. +This can happen e.g when you change the tablespace of a table. See +log_newpage() in heapam.c. + +You can use pg_xlogdump to see which WAL record a page image corresponds +to. But beware that the LSN in the page image points to the *end* of the +WAL record, while the LSN that pg_xlogdump prints is the *beginning* of +the WAL record. So to find which WAL record a page image corresponds to, +find the LSN from the page image in pg_xlogdump output, and back off one +record. (you can't just grep for the line containing the LSN). diff --git a/contrib/page_image_logging/postprocess-images.c b/contrib/page_image_logging/postprocess-images.c new file mode 100644 index 000..6b4ab4c --- /dev/null +++
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 03:28 PM, Stephen Frost wrote: * Simon Riggs (si...@2ndquadrant.com) wrote: +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Agreed. I'll add it to the GSoC ideas page. I don't think this is a good GSoC project. Documentation-only projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work). Perhaps you can argue that a sample database is not documentation, but it's certainly not in the spirit of the program. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 23 April 2014 13:46, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 04/23/2014 03:28 PM, Stephen Frost wrote: * Simon Riggs (si...@2ndquadrant.com) wrote: +1 to the idea of an example database, used throughout the docs Sounds like a summer of code project. Agreed. I'll add it to the GSoC ideas page. I don't think this is a good GSoC project. Documentation-only projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work). Perhaps you can argue that a sample database is not documentation, but it's certainly not in the spirit of the program. Out of curiosity, had anyone seen this which some of us were briefly working on last year at PgCon's unconference? https://github.com/pvh/postgresql-sample-database -- 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] 9.4 Proposal: Initdb creates a single table
Heikki, * Heikki Linnakangas (hlinnakan...@vmware.com) wrote: On 04/23/2014 03:28 PM, Stephen Frost wrote: Agreed. I'll add it to the GSoC ideas page. I don't think this is a good GSoC project. Documentation-only projects are not eligible for GSoC (https://www.google-melange.com/gsoc/document/show/gsoc_program/google/gsoc2014/help_page#12._Are_proposals_for_documentation_work). Ah, right, of course. Sorry, I've gotten to thinking of the GSoC page as being a TODO but for small projects. :) Perhaps you can argue that a sample database is not documentation, but it's certainly not in the spirit of the program. Nah, that wasn't my intent at all. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
Bruce Momjian br...@momjian.us writes: On Wed, Apr 23, 2014 at 10:20:42AM +0200, Petr Jelinek wrote: On 23/04/14 00:40, Peter Geoghegan wrote: On Tue, Apr 22, 2014 at 3:32 PM, Bruce Momjian br...@momjian.us wrote: Where are we on the default JSONB opclass change? FWIW, I still don't have any strong opinion here. I defer to others on this question. I vote for changing it, even though neither option is ideal I think that given the nature of datatype the current default will break inserts for common usage pattern and that's much worse than not being able to use the index for some operators. I agree. We should choose the most general option as the default. That seems to be the consensus, but now we need a name for the soon-to-be-not-default opclass. What's a good short adjective for it? 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] Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Wed, Apr 23, 2014 at 10:20:42AM +0200, Petr Jelinek wrote: On 23/04/14 00:40, Peter Geoghegan wrote: On Tue, Apr 22, 2014 at 3:32 PM, Bruce Momjian br...@momjian.us wrote: Where are we on the default JSONB opclass change? FWIW, I still don't have any strong opinion here. I defer to others on this question. I vote for changing it, even though neither option is ideal I think that given the nature of datatype the current default will break inserts for common usage pattern and that's much worse than not being able to use the index for some operators. I agree. We should choose the most general option as the default. That seems to be the consensus, but now we need a name for the soon-to-be-not-default opclass. What's a good short adjective for it? comprehensive? Not particularly short ... According to Merriam Webster: Synonyms all-embracing, all-in [chiefly British], all-inclusive, broad-gauge (or broad-gauged), compendious, complete, encyclopedic, cover-all, cyclopedic, embracive, exhaustive, full, global, inclusive, in-depth, omnibus, panoramic, thorough, universal Related Words broad, catholic, encyclical, general, inclusionary, overall; cosmic (also cosmical), extensive, far, far-reaching, grand, large, panoptic, sweeping, vast, wide, wide-ranging; blanket, indiscriminate, unrestricted jsonb_omnibus_ops ? -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] aggregate returning anyarray and 'cannot determine result data type'
I wrote: The same problem came up in connection with the ordered set aggregates that were added recently, and that patch implemented an interesting workaround: the final function for an OSA gets additional dummy arguments of the same type as the aggregate inputs. They are always passed as NULLs at runtime, and have no real value except if the aggregate is polymorphic --- but when it is, they provide a way to resolve the result type of a polymorphic final function, even if the state type is internal or otherwise non-polymorphic. I thought at the time that maybe we should offer this feature for regular aggregates as well as ordered-set ones, but didn't do anything about it because there hadn't been demand. After sleeping on it, I'm convinced that this was an oversight that we should fix before 9.4 ships. The code changes should be pretty minimal; the executor in particular probably needs *less* code to do this in a uniform way. One potential issue though is that if sample_final existed in both signatures it wouldn't be very clear which one got selected for the aggregate. Perhaps the best fix would be to invent a different CREATE AGGREGATE keyword for finalfns with extra arguments? To be concrete: let's add a new boolean parameter with the semantics of final function takes extra dummy arguments (default false). There would need to be one for the separate moving-aggregate final function too, of course. The best naming idea I've got right now is finalfunc_extra and mfinalfunc_extra, but maybe somebody can do better? 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] Re: default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: That seems to be the consensus, but now we need a name for the soon-to-be-not-default opclass. What's a good short adjective for it? comprehensive? Not particularly short ... According to Merriam Webster: Synonyms all-embracing, all-in [chiefly British], all-inclusive, broad-gauge (or broad-gauged), compendious, complete, encyclopedic, cover-all, cyclopedic, embracive, exhaustive, full, global, inclusive, in-depth, omnibus, panoramic, thorough, universal Related Words broad, catholic, encyclical, general, inclusionary, overall; cosmic (also cosmical), extensive, far, far-reaching, grand, large, panoptic, sweeping, vast, wide, wide-ranging; blanket, indiscriminate, unrestricted jsonb_omnibus_ops ? hm ... jsonb_full_ops seems nicely short, but on the other hand it just begs the question full what?. I'm a bit worried about future-proof-ness too; what if somebody later comes up with a new opclass that indexes more operators? We'd end up calling it jsonb_fuller_ops, ick. I was kind of hoping for a technical adjective, like hash is for the soon-to-be-default opclass. What is it about this opclass that distinguishes it from other indexing approaches that someone might try? 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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 10:11 AM, Simon Riggs si...@2ndquadrant.com wrote: We start with a database called Postgres and a schema called Public. Yet we don't start up with any usable tables. I propose we add a single table called Postgres when we Initdb CREATE TABLE Postgres (Id Integer, Data Jsonb); COMMENT ON TABLE Postgres IS 'Single table for quick start usage - design your database'; The purpose of this is to make the database immediately usable. By including this table in the default initdb it will mean that programs can rely on the existence of this table and begin working quicker. By now, some of you will be doubled over laughing as if this is an April fool joke. I don't mean it to be at all. I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. -- With best regards, Alexander Korotkov.
[HACKERS] Review: ECPG FETCH readahead
I haven't been too familiar with the ECPG internals so far but tried to do my best. Generic criteria * Does it follow the project coding guidelines? Yes. * Are there portability issues? Shouldn't be. I even noticed the code tries to avoid platform-specific behaviour of standard library function - see comment about strtoll() in Linux in 25.patch. (I personally don't know how that function works elsewhere but that shouldn't matter.) * Are the comments sufficient and accurate? Yes, mostly. Just a few improvements recommended below. * Does it do what it says, correctly? IMO, yes. * Does it produce compiler warnings? No. * Can you make it crash? No. Only some of the parts deserve comments: 23.patch Reviewed earlier as a component of the relate patch (http://www.postgresql.org/message-id/52a1e61a.7010...@gmail.com) and minimum changes done since that time. Nevertheless, a few more comments: * How about a regression test for the new ECPGcursor_dml() function? * ECPGtrans() - arguments are explained, but the return (bool) value should be as well. * line breaks (pgindent might help): static void output_cursor_name(struct cursor *ptr) { instead of static void output_cursor_name(struct cursor *ptr) { * confused messages in src/interfaces/ecpg/test/sql/cursorsubxact.pgc, starting at 100: exec sql open :curname; if (sqlca.sqlcode 0) printf(open %s (case sensitive) failed with SQLSTATE %5s\n, curname, sqlca.sqlstate); else printf(close %s (case sensitive) succeeded\n, curname); I suppose both should be open. 26.patch (the READAHEAD feature itself) --- I tried to understand the code but couldn't find any obvious error. The coding style looks clean. Maybe just the arguments and return value of the ecpglib functinons (ECPGopen, ECPGopen, ECPGfetch, ...) deserve a bit more attention. As for tests, I find them comprehensive and almost everything they do is clear to me. Just the following was worth questions: * sql-cursor-ra-fetch.stderr [NO_PID]: ecpg_execute on line 169: query: move forward all in scroll_cur; with 0 parameter(s) on connection regress1 ... [NO_PID]: ecpg_execute on line 169: query: move relative -3 in scroll_cur; with 0 parameter(s) on As the first iteration is special anyway, wouldn't move absolute -3 be more efficient than the existing 2 commands? The following test (also FETCH RELATIVE) uses move absolute: [NO_PID]: ecpg_execute on line 186: query: move absolute -20 in scroll_cur; with 0 parameter(s) on connection regress1 Other than this, I had an idea to improve the behaviour if READAHEAD is smaller than the actual step, but then realized that 29.patch actually does fix that :-) * cursor-ra-move.pgc What's the relevant difference between unspec_cur1 and unspec_cur2 cursors? There's no difference in scrollability or ordering. And the tests seem to be identical. * cursor-ra-swdir.pgc No questions * cursorsubxact.pgc This appears to be the test we discussed earlier: http://www.postgresql.org/message-id/52a1cab6.9020...@cybertec.at The only difference I see is minor change of log message of DECLARE command. Therefore I didn't have to recheck the logic of the test. 28.patch * ecpg_cursor_do_move_absolute() and ecpg_cursor_do_move_all() should better be declared in 26.patch. Just a pedantic comment - all the parts will probably be applied all at once. Other - Besides the individual parts I propose some typo fixes and improvements in wording: * doc/src/sgml/ecpg.sgml 462c462 ECPG does cursor accounting in its runtime library and this makes possible --- ECPG does cursor accounting in its runtime library and this makes it possible 504c504 recommended to recompile using option option-r readahead=number/option --- recommended to recompile it using option option-r readahead=number/option * src/interfaces/ecpg/ecpglib/extern.h 97c97 * The cursor was created in this level of * (sub-)transaction. --- * The cursor was created at this level of (sub-)transaction. * src/interfaces/ecpg/ecpglib/README.cursor+subxact 4c4 Contents of tuples returned by a cursor always reflect the data present at --- Contents of tuples returned by a cursor always reflects the data present at 29c29 needs two operations. If the next command issued by the application spills --- need two operations. If the next command issued by the application spills 32c32 kinds of commands as is after 3 cache misses. FETCH FORWARD/BACKWARD allows --- kinds of commands as is after 3 cache misses. FETCH FORWARD/BACKWARD allows 81c81 I can also be negative (but also 1-based) if the application started with --- It can also be negative (but also 1-based) if the application started with 132c132 is that (sub-)transactions are also needed to be tracked. These two are --- is that (sub-)transactions also need to
Re: [HACKERS] Review: ECPG FETCH readahead
Antonin Houska wrote: I haven't been too familiar with the ECPG internals so far but tried to do my best. I'm afraid we're stuck on this patch until Michael has time to review it, or some other committer wants to acquire maintainership rights in the ECPG code. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 4:50 AM, Simon Riggs si...@2ndquadrant.com wrote: On 23 April 2014 07:14, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: By now, some of you will be doubled over laughing as if this is an April fool joke. Indeed. I do like to share the odd joke now and then, it has to be said. So I'm glad I enriched your day. I was taught that ideas are accepted in this order: first we think them a joke, then we perceive them as a threat, then they become obvious. I can't find a Wikipedia article to give that idea more weight (OK, that was a joke). Just trying to think about how to improve the out of the box experience in ways that others already consider obvious. Mahatma Gandhi said: First they ignore you, then they laugh at you, then they fight you, then you win. ;-) More info in [1] [2]. Regards, [1] http://en.wikiquote.org/wiki/Mahatma_Gandhi [2] http://www.brainyquote.com/quotes/quotes/m/mahatmagan103630.html -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog sobre TI: http://fabriziomello.blogspot.com Perfil Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello
Re: [HACKERS] Review: ECPG FETCH readahead
Hi, thanks for the review. 2014-04-23 17:20 keltezéssel, Antonin Houska írta: I haven't been too familiar with the ECPG internals so far but tried to do my best. Generic criteria * Does it follow the project coding guidelines? Yes. * Are there portability issues? Shouldn't be. I even noticed the code tries to avoid platform-specific behaviour of standard library function - see comment about strtoll() in Linux in 25.patch. (I personally don't know how that function works elsewhere but that shouldn't matter.) * Are the comments sufficient and accurate? Yes, mostly. Just a few improvements recommended below. * Does it do what it says, correctly? IMO, yes. * Does it produce compiler warnings? No. * Can you make it crash? No. Only some of the parts deserve comments: 23.patch Reviewed earlier as a component of the relate patch (http://www.postgresql.org/message-id/52a1e61a.7010...@gmail.com) and minimum changes done since that time. Nevertheless, a few more comments: * How about a regression test for the new ECPGcursor_dml() function? It makes sense to add one. * ECPGtrans() - arguments are explained, but the return (bool) value should be as well. All exported ECPG functions returns bool. IIRC the code generated by EXEC SQL WHENEVER something-else-than-CONTINUE makes use of the returned value. * line breaks (pgindent might help): static void output_cursor_name(struct cursor *ptr) { instead of static void output_cursor_name(struct cursor *ptr) { OK. * confused messages in src/interfaces/ecpg/test/sql/cursorsubxact.pgc, starting at 100: exec sql open :curname; if (sqlca.sqlcode 0) printf(open %s (case sensitive) failed with SQLSTATE %5s\n, curname, sqlca.sqlstate); else printf(close %s (case sensitive) succeeded\n, curname); I suppose both should be open. Yes, oversight. 26.patch (the READAHEAD feature itself) --- I tried to understand the code but couldn't find any obvious error. The coding style looks clean. Maybe just the arguments and return value of the ecpglib functinons (ECPGopen, ECPGopen, ECPGfetch, ...) deserve a bit more attention. What do you mean exactly? As for tests, I find them comprehensive and almost everything they do is clear to me. Just the following was worth questions: * sql-cursor-ra-fetch.stderr [NO_PID]: ecpg_execute on line 169: query: move forward all in scroll_cur; with 0 parameter(s) on connection regress1 ... [NO_PID]: ecpg_execute on line 169: query: move relative -3 in scroll_cur; with 0 parameter(s) on As the first iteration is special anyway, wouldn't move absolute -3 be more efficient than the existing 2 commands? The caching code tries to do the correct thing whichever direction the cursor is scanned. AFAIR this one explicitly tests invalidating the readahead window if you fall off it by using MOVE FORWARD ALL. The following test (also FETCH RELATIVE) uses move absolute: [NO_PID]: ecpg_execute on line 186: query: move absolute -20 in scroll_cur; with 0 parameter(s) on connection regress1 Other than this, I had an idea to improve the behaviour if READAHEAD is smaller than the actual step, but then realized that 29.patch actually does fix that :-) B-) * cursor-ra-move.pgc What's the relevant difference between unspec_cur1 and unspec_cur2 cursors? There's no difference in scrollability or ordering. And the tests seem to be identical. Oh. Erm. That is a leftover from a previous incarnation when I thought it's a good idea to let the server return the actual scrollability flag because the server can actually know. The simple query when running in psql is implicitly scrollable but the query with the join is not. That idea was shot down with prejudice but I forgot to adjust the test and remove one of these cursors. Now all queries where scrollability is not specified are explicitly modified (behind the application's back) to have NO SCROLL. ( Please, don't revise your previous opinion, Tom Lane included... ;-) ) * cursor-ra-swdir.pgc No questions * cursorsubxact.pgc This appears to be the test we discussed earlier: http://www.postgresql.org/message-id/52a1cab6.9020...@cybertec.at The only difference I see is minor change of log message of DECLARE command. Therefore I didn't have to recheck the logic of the test. 28.patch * ecpg_cursor_do_move_absolute() and ecpg_cursor_do_move_all() should better be declared in 26.patch. Just a pedantic comment - all the parts will probably be applied all at once. When I re-roll the patchset, I will move this chunk to 26. Other - Besides the individual parts I propose some typo fixes and improvements in wording: * doc/src/sgml/ecpg.sgml 462c462 ECPG does cursor accounting in its runtime library and this makes possible --- ECPG does cursor accounting in its runtime library and this makes
Re: [HACKERS] assertion failure 9.3.4
Alvaro Herrera wrote: I'm thinking about the comparison of full infomask as you propose instead of just the bits that we actually care about. I think the only thing that could cause a spurious failure (causing an extra execution of the HeapTupleSatisfiesUpdate call and the stuff below) is somebody setting HEAP_XMIN_COMMITTED concurrently; but that seems infrequent enough that it should pretty harmless. However, should we worry about possible future infomask bit changes that could negatively affect this behavior? Here's a complete patch illustrating what I mean. This is slightly more expensive than straight infomask comparison in terms of machine instructions, but that seems okay to me. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services *** a/src/backend/access/heap/heapam.c --- b/src/backend/access/heap/heapam.c *** *** 2586,2591 compute_infobits(uint16 infomask, uint16 infomask2) --- 2586,2612 } /* + * Given two versions of the same t_infomask for a tuple, compare them and + * return whether the relevant status for a tuple Xmax has changed. This is + * used after a buffer lock has been released and reacquired: we want to ensure + * that the tuple state continues to be the same it was when we previously + * examined it. + * + * Note the Xmax field itself must be compared separately. + */ + static inline bool + xmax_infomsk_changed(uint16 infomask1, uint16 infomask2) + { + const uint16 interesting = + HEAP_XMAX_IS_MULTI | HEAP_XMAX_LOCK_ONLY | HEAP_LOCK_MASK; + + if ((infomask1 interesting) != (infomask2 interesting)) + return true; + + return false; + } + + /* * heap_delete - delete a tuple * * NB: do not call this directly unless you are prepared to deal with *** *** 2722,2728 l1: * update this tuple before we get to this point. Check for xmax * change, and start over if so. */ ! if (!(tp.t_data-t_infomask HEAP_XMAX_IS_MULTI) || !TransactionIdEquals(HeapTupleHeaderGetRawXmax(tp.t_data), xwait)) goto l1; --- 2743,2749 * update this tuple before we get to this point. Check for xmax * change, and start over if so. */ ! if (xmax_infomsk_changed(tp.t_data-t_infomask, infomask) || !TransactionIdEquals(HeapTupleHeaderGetRawXmax(tp.t_data), xwait)) goto l1; *** *** 2748,2754 l1: * other xact could update this tuple before we get to this point. * Check for xmax change, and start over if so. */ ! if ((tp.t_data-t_infomask HEAP_XMAX_IS_MULTI) || !TransactionIdEquals(HeapTupleHeaderGetRawXmax(tp.t_data), xwait)) goto l1; --- 2769,2775 * other xact could update this tuple before we get to this point. * Check for xmax change, and start over if so. */ ! if (xmax_infomsk_changed(tp.t_data-t_infomask, infomask) || !TransactionIdEquals(HeapTupleHeaderGetRawXmax(tp.t_data), xwait)) goto l1; *** *** 3275,3281 l2: * update this tuple before we get to this point. Check for xmax * change, and start over if so. */ ! if (!(oldtup.t_data-t_infomask HEAP_XMAX_IS_MULTI) || !TransactionIdEquals(HeapTupleHeaderGetRawXmax(oldtup.t_data), xwait)) goto l2; --- 3296,3302 * update this tuple before we get to this point. Check for xmax * change, and start over if so. */ ! if (xmax_infomsk_changed(oldtup.t_data-t_infomask, infomask) || !TransactionIdEquals(HeapTupleHeaderGetRawXmax(oldtup.t_data), xwait)) goto l2; *** *** 3324,3335 l2: if (HEAP_XMAX_IS_KEYSHR_LOCKED(infomask) key_intact) { LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); - /* * recheck the locker; if someone else changed the tuple while * we weren't looking, start over. */ ! if ((oldtup.t_data-t_infomask HEAP_XMAX_IS_MULTI) || !TransactionIdEquals( HeapTupleHeaderGetRawXmax(oldtup.t_data), xwait)) --- 3345,3355 if (HEAP_XMAX_IS_KEYSHR_LOCKED(infomask) key_intact) { LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE); /* * recheck the locker; if someone else changed the tuple while * we weren't looking, start over. */ ! if (xmax_infomsk_changed(oldtup.t_data-t_infomask, infomask) || !TransactionIdEquals( HeapTupleHeaderGetRawXmax(oldtup.t_data), xwait)) *** *** 3350,3356 l2: * some other xact could update this tuple before we get to * this point. Check for xmax change, and start over if so. */ ! if ((oldtup.t_data-t_infomask HEAP_XMAX_IS_MULTI) || !TransactionIdEquals( HeapTupleHeaderGetRawXmax(oldtup.t_data), xwait)) --- 3370,3376 * some other xact
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
Josh Berkus wrote: On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. We should totally have a type uuidserial. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. I think what we should do here is that if we see that XMAX_INVALID is set, we just reset everything to zero without checking the multixact contents. Something like the attached (warning: hand-edited, line numbers might be bogus) I still don't know under what circumstances this situation could arise. This seems most strange to me. I would wonder about this to be just papering over a different bug elsewhere, except that we know this tuple comes from a pg_upgraded table and so I think the only real solution is to cope. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 9283b70..72602fd 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -5585,7 +5602,12 @@ heap_prepare_freeze_tuple(HeapTupleHeader tuple, TransactionId cutoff_xid, */ xid = HeapTupleHeaderGetRawXmax(tuple); - if (tuple-t_infomask HEAP_XMAX_IS_MULTI) + if ((tuple-t_infomask HEAP_XMAX_IS_MULTI) + (tuple-t_infomask HEAP_XMAX_INVALID)) + { + freeze_xmax = true; + } + else if (tuple-t_infomask HEAP_XMAX_IS_MULTI) { TransactionId newxmax; uint16 flags; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
On Wed, Apr 23, 2014 at 03:01:02PM -0300, Alvaro Herrera wrote: Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. I think what we should do here is that if we see that XMAX_INVALID is set, we just reset everything to zero without checking the multixact contents. Something like the attached (warning: hand-edited, line numbers might be bogus) I still don't know under what circumstances this situation could arise. This seems most strange to me. I would wonder about this to be just papering over a different bug elsewhere, except that we know this tuple comes from a pg_upgraded table and so I think the only real solution is to cope. Shouldn't we log something at least if we are unsure of the cause? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
Bruce Momjian wrote: On Wed, Apr 23, 2014 at 03:01:02PM -0300, Alvaro Herrera wrote: Andres Freund wrote: On 2014-03-31 08:54:53 -0300, Alvaro Herrera wrote: My conclusion here is that some part of the code is failing to examine XMAX_INVALID before looking at the value stored in xmax itself. There ought to be a short-circuit. Fortunately, this bug should be pretty harmless. .. and after looking, I'm fairly sure the bug is in heap_tuple_needs_freeze. heap_tuple_needs_freeze() isn't *allowed* to look at XMAX_INVALID. Otherwise it could miss freezing something still visible on a standby or after an eventual crash. I think what we should do here is that if we see that XMAX_INVALID is set, we just reset everything to zero without checking the multixact contents. Something like the attached (warning: hand-edited, line numbers might be bogus) I still don't know under what circumstances this situation could arise. This seems most strange to me. I would wonder about this to be just papering over a different bug elsewhere, except that we know this tuple comes from a pg_upgraded table and so I think the only real solution is to cope. Shouldn't we log something at least if we are unsure of the cause? I don't know. Is it possible that XMAX_IS_MULTI got set because of cosmic rays? At this point that's the only explanation that makes sense to me. And I'm not sure what to do about this until we know more -- more user reports of this problem, for instance. I don't see any reasonable way to distinguish this particular kind of multixact-out-of-bounds situation from any other, so not sure what else to log either (you can see that we already emit an error message.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
On Wed, Apr 23, 2014 at 03:42:14PM -0300, Alvaro Herrera wrote: I still don't know under what circumstances this situation could arise. This seems most strange to me. I would wonder about this to be just papering over a different bug elsewhere, except that we know this tuple comes from a pg_upgraded table and so I think the only real solution is to cope. Shouldn't we log something at least if we are unsure of the cause? I don't know. Is it possible that XMAX_IS_MULTI got set because of cosmic rays? At this point that's the only explanation that makes sense to me. And I'm not sure what to do about this until we know more -- more user reports of this problem, for instance. I don't see any reasonable way to distinguish this particular kind of multixact-out-of-bounds situation from any other, so not sure what else to log either (you can see that we already emit an error message.) I guess I am lost then. I thought it supressed the error. What does the patch do? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
Bruce Momjian wrote: On Wed, Apr 23, 2014 at 03:42:14PM -0300, Alvaro Herrera wrote: I still don't know under what circumstances this situation could arise. This seems most strange to me. I would wonder about this to be just papering over a different bug elsewhere, except that we know this tuple comes from a pg_upgraded table and so I think the only real solution is to cope. Shouldn't we log something at least if we are unsure of the cause? I don't know. Is it possible that XMAX_IS_MULTI got set because of cosmic rays? At this point that's the only explanation that makes sense to me. And I'm not sure what to do about this until we know more -- more user reports of this problem, for instance. I don't see any reasonable way to distinguish this particular kind of multixact-out-of-bounds situation from any other, so not sure what else to log either (you can see that we already emit an error message.) I guess I am lost then. I thought it supressed the error. What does the patch do? You're right, it does. I am not sure I would apply it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
On April 23, 2014 8:51:21 PM CEST, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Bruce Momjian wrote: On Wed, Apr 23, 2014 at 03:42:14PM -0300, Alvaro Herrera wrote: I still don't know under what circumstances this situation could arise. This seems most strange to me. I would wonder about this to be just papering over a different bug elsewhere, except that we know this tuple comes from a pg_upgraded table and so I think the only real solution is to cope. Shouldn't we log something at least if we are unsure of the cause? I don't know. Is it possible that XMAX_IS_MULTI got set because of cosmic rays? At this point that's the only explanation that makes sense to me. And I'm not sure what to do about this until we know more -- more user reports of this problem, for instance. I don't see any reasonable way to distinguish this particular kind of multixact-out-of-bounds situation from any other, so not sure what else to log either (you can see that we already emit an error message.) I guess I am lost then. I thought it supressed the error. What does the patch do? You're right, it does. I am not sure I would apply it. I think this patch is a seriously bad idea. For one, it's not actually doing anything about the problem - the tuple can be accessed without freezing getting involved. For another, it will increase wall traffic for freezing of short lived tuples considerably, without any benefit. Andres -- Please excuse brevity and formatting - I am writing this on my mobile phone. Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 02:26:50PM -0300, Alvaro Herrera wrote: Josh Berkus wrote: On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. We should totally have a type uuidserial. Something like it, certainly. One thing SQL Server does right is to have an opaque identity column, for which UUID would do an admirable job. We would need to build UUID functionality in, and I don't see this as a hard task. Should I draft it up as a self-contained extension? 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] MultiXactId error after upgrade to 9.3.4
Andres Freund wrote: I think this patch is a seriously bad idea. For one, it's not actually doing anything about the problem - the tuple can be accessed without freezing getting involved. Normal access other than freeze is not a problem, because other code paths do check for HEAP_XMAX_INVALID and avoid access to Xmax if that's set. For another, it will increase wall traffic for freezing of short lived tuples considerably, without any benefit. True. I didn't actually try to run this; it was just for demonstration purposes. It'd need some cooperation from heap_tuple_needs_freeze in order to work at all, for one thing. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade and epoch
On Wed, Apr 23, 2014 at 5:26 AM, Bruce Momjian br...@momjian.us wrote: Sergey, are you seeing a problem only because you are interacting with other systems that didn't reset their epoch? I faced this after upgrading clusters with PgQ Skytools3 installed only. They didn't interact with any other systems. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MultiXactId error after upgrade to 9.3.4
On 2014-04-23 16:30:05 -0300, Alvaro Herrera wrote: Andres Freund wrote: I think this patch is a seriously bad idea. For one, it's not actually doing anything about the problem - the tuple can be accessed without freezing getting involved. Normal access other than freeze is not a problem, because other code paths do check for HEAP_XMAX_INVALID and avoid access to Xmax if that's set. For another, it will increase wall traffic for freezing of short lived tuples considerably, without any benefit. True. I didn't actually try to run this; it was just for demonstration purposes. It'd need some cooperation from heap_tuple_needs_freeze in order to work at all, for one thing. I think if you want to add something like this it should be added *inside* the if (MultiXactIdPrecedes(multi, cutoff_multi)) block in FreezeMultiXactId(). There it seems to make quite a bit of sense. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] All caught up
On Fri, Apr 18, 2014 at 07:42:06AM +, Albe Laurenz wrote: Bruce Momjian wrote: I suggest the attached documentation fix. Patch applied and backpatched to 9.3. Thanks. What would PostgreSQL do without Bruce who undertakes the Herculean task of making sure that nothing gets forgotten and slips through the cracks? Thanks. FYI, I finished going through all the unresolved email threads yesterday, so I have done as much as I can to prepare for beta. I will start the 9.4 major release notes now. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: ECPG FETCH readahead
[Now I'm only replying where my explanation seems useful. If you expect anything else, please remind me.] On 04/23/2014 06:41 PM, Boszormenyi Zoltan wrote: All exported ECPG functions returns bool. IIRC the code generated by EXEC SQL WHENEVER something-else-than-CONTINUE makes use of the returned value. ok 26.patch (the READAHEAD feature itself) --- Maybe just the arguments and return value of the ecpglib functinons (ECPGopen, ECPGopen, ECPGfetch, ...) deserve a bit more attention. What do you mean exactly? Basically the missing description of return type was most blatant, but you explained it above. Now that I see some of the existing library functions, the descriptions of parameters are neither too eloquent. So ignore this remark. * sql-cursor-ra-fetch.stderr [NO_PID]: ecpg_execute on line 169: query: move forward all in scroll_cur; with 0 parameter(s) on connection regress1 ... [NO_PID]: ecpg_execute on line 169: query: move relative -3 in scroll_cur; with 0 parameter(s) on As the first iteration is special anyway, wouldn't move absolute -3 be more efficient than the existing 2 commands? The caching code tries to do the correct thing whichever direction the cursor is scanned. AFAIR this one explicitly tests invalidating the readahead window if you fall off it by using MOVE FORWARD ALL. I have no doubt about correctness of the logic, just suspect that a single MOVE command could do the action. Perhaps consider it my paranoia and let committer judge if it's worth a change (especially if the related amount of coding would seem inadequate). Other - Besides the individual parts I propose some typo fixes and improvements in wording: * doc/src/sgml/ecpg.sgml In general, I'm not English native speaker, can be wrong in some cases. Just pointed out what I thought is worth checking. // Tony -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: ECPG FETCH readahead
On 04/23/2014 05:24 PM, Alvaro Herrera wrote: Antonin Houska wrote: I haven't been too familiar with the ECPG internals so far but tried to do my best. I'm afraid we're stuck on this patch until Michael has time to review it, or some other committer wants to acquire maintainership rights in the ECPG code. Committer availability might well be the issue, but missing review probably too. Whether this review is enough to move the patch to ready for committer - I tend to let the next CFM decide. (I don't find it productive to ignite another round of discussion about kinds of reviews - already saw some.) // Tony -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 12:24:21PM -0700, David Fetter wrote: On Wed, Apr 23, 2014 at 02:26:50PM -0300, Alvaro Herrera wrote: Josh Berkus wrote: On 04/23/2014 07:43 AM, Alexander Korotkov wrote: I can propose contrib PostgreNoSQL providing following: 1) Table postgres as you proposed. 2) Functions: get_postgres(id intgeger) returns jsonb, set_postgres(id integer, data jsonb) returns void, search_postgres(query jsonb) returns setof postgres. search_postgres will have semantics of @ jsonb operator 3) Background workers which provides HTTP wrapper over those functions. You're forgetting ... sharding/replication over multiple masters. Also, the id should be a text value so that folks can use hash keys, or whatever. We should totally have a type uuidserial. Something like it, certainly. One thing SQL Server does right is to have an opaque identity column, for which UUID would do an admirable job. We would need to build UUID functionality in, and I don't see this as a hard task. Should I draft it up as a self-contained extension? So I did a little research, and it appears that there's a part of util-linux that does UUIDs and is available under the 3-clause BSDL. It only does time- and urandom-based UUIDs, but that's probably a better start than nothing. Is there any good reason not to roll native UUID generation into our standard distribution? 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] Perfomance degradation 9.3 (vs 9.2) for FreeBSD
Included is the graph (from PostgreSQL Enterprise Consortium's 2014 report page 13: https://www.pgecons.org/downloads/43). I see up to 14% degration (at 128 concurrent users) comparing with 9.2. That URL returns 'Forbidden'... Sorry for this. I sent a problem report to the person in charge. In the mean time, please go to: https://www.pgecons.org/download/works_2013/ then click the link 2013 年度WG1活動報告 (sorry for not English). You should be able to download a report (PDF). Also the report is written in Japanese. I hope you can read at leat the graph in page 13 and the table in page 14. Is pgecons planning to do a translation of that at some point? It looks like good material, and the audience able to understand it is rather limited now :) Yeah, once I proposed a translation of the documents by professional translators to the organization. Their decision was no. The main reason was cost. The document is huge and the translation work could cost tremendously. So unless someone comes up for volunteering the translation work, the document would not be translated. Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.4 Proposal: Initdb creates a single table
David Fetter da...@fetter.org writes: Is there any good reason not to roll native UUID generation into our standard distribution? It's already there (as of 9.4) in pg_crypto. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] extension_control_path
On Mon, 2014-03-10 at 09:36 +0100, Dimitri Fontaine wrote: Please find attached the v2 version of the patch, including fixes for the crash and documentation aspects you've listed before. Do we want to get this version committed (will need some small tweaks), or do we want to wait for the next release for a more comprehensive solution? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] extension_control_path
* Peter Eisentraut (pete...@gmx.net) wrote: On Mon, 2014-03-10 at 09:36 +0100, Dimitri Fontaine wrote: Please find attached the v2 version of the patch, including fixes for the crash and documentation aspects you've listed before. Do we want to get this version committed (will need some small tweaks), or do we want to wait for the next release for a more comprehensive solution? I'm alright with this and it's in line with the other parameters that we support. I had hoped for a different approach, but I seem to be in the minority camp and this is more in line with what we've got already anyway. I do think it's better than what we've got today, and the ability to set it runtime makes it useable for a lot of things. Perhaps we could make that not require superuser at some point, provided the extension which gets installed from the path set by the user doesn't have anything in it which requires superuser. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] extension_control_path
Peter Eisentraut pete...@gmx.net writes: On Mon, 2014-03-10 at 09:36 +0100, Dimitri Fontaine wrote: Please find attached the v2 version of the patch, including fixes for the crash and documentation aspects you've listed before. Do we want to get this version committed (will need some small tweaks), or do we want to wait for the next release for a more comprehensive solution? I think it's missed the window for 9.4, especially since the change was still controversial IIRC. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: ECPG FETCH readahead
On Thu, Apr 24, 2014 at 12:15:41AM +0200, Antonin Houska wrote: Whether this review is enough to move the patch to ready for committer - I tend to let the next CFM decide. (I don't find it productive to ignite another round of discussion about kinds of reviews - already saw some.) In today's CF process, we trust reviewers to make that decision. When all unambiguous defects known to you have been resolved, please mark the patch Ready for Committer. Your review covered more ground than the average review, so don't worry about it being too cursory to qualify. Thanks, nm -- Noah Misch 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] Perfomance degradation 9.3 (vs 9.2) for FreeBSD
On Apr 22, 2014, at 5:07 PM, Andrew Dunstan and...@dunslane.net wrote: On 04/22/2014 06:43 PM, Mark Wong wrote: On Tue, Apr 22, 2014 at 10:06 AM, Joshua D. Drake j...@commandprompt.com mailto:j...@commandprompt.com wrote: On 04/22/2014 08:26 AM, Andrew Dunstan wrote: I'm going away tomorrow for a few days RR. when I'm back next week I will set up a demo client running this module. If you can have a machine prepped for this purpose by then so much the better, otherwise I will have to drag out a box I recently rescued and have been waiting for something to use it with. It's more important that it's stable (i.e. nothing else running on it) than that it's very powerful. It could be running Ubuntu or some Redhattish variant or, yes, even FreeBSD. This is best handled by Mark. Mark can you help Andrew with this? I assume we would use the DL385 with the MS70? Yeah, I can help. But let me know if Alfred's offer is preferred. I don't think they are mutually exclusive, but I'd rather start off with one machine. I would find it easiest if it were on something like CentOS6.5. When we have that running and reporting like we want it we can add a FreeBSD server. The idea is that these machines would be available for a long time, ideally quite a few years. We want to have them with a stable time series of performance data so that when something disturbs the performance it sticks out like a sore thumb. Ok, centos 6.4 is on there now, I'll try to get that upgraded within a few days or so. I'll keep you posted. Regards, Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Perfomance degradation 9.3 (vs 9.2) for FreeBSD
On Mon, Apr 21, 2014 at 11:25:35PM +0200, Andres Freund wrote: On 2014-04-21 17:21:20 -0400, Bruce Momjian wrote: On Mon, Apr 21, 2014 at 02:08:51PM -0700, Joshua Drake wrote: If the community had more *BSD presence I think it would be great but it isn't all that viable at this point. I do know however that no-one in this community would turn down a team of FreeBSD advocates helping us make PostgreSQL awesome for PostgreSQL. I don't think we would even implement a run-time control for Linux or Windows for this, so I don't even think it is a FreeBSD issue. I think some of the arguments in this thread are pretty damn absurd. We have just introduced dynamic_shared_memory_type. I agree. The ideal is nobody wishing for an option, but I'd rather have the option if a non-theoretical set of users is feeling the pain of its absence. -- Noah Misch 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] 9.4 Proposal: Initdb creates a single table
On Wed, Apr 23, 2014 at 08:27:52PM -0400, Tom Lane wrote: David Fetter da...@fetter.org writes: Is there any good reason not to roll native UUID generation into our standard distribution? It's already there (as of 9.4) in pg_crypto. Sorry I wasn't clear enough. Since contrib/pgcrypto is a module that might well not be installed, people can't just build software for PostgreSQL and have UUIDs available, certainly not in the sense that, for example, BIGSERIAL is. 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] Runing DBT2 on Postgresql
On Wed, Apr 23, 2014 at 2:33 AM, Rohit Goyal rhtgyl...@gmail.com wrote: I am trying to install dbt2 on postgresql database. cmake(configure) command work fine and but make command(build) give an error given below. I have no idea about how to solve it ld has become less tolerant of certain flag orderings over time in certain distros. The following tweak may be used as a quick-and-dirty work around: diff --git a/CMakeLists.txt b/CMakeLists.txt index 6a128e3..f6a796b 100644 --- a/CMakeLists.txt +++ b/CMakeLists.txt @@ -11,6 +11,7 @@ SET(DBT2_CLIENT bin/dbt2-client) SET(DBT2_DATAGEN bin/dbt2-datagen) SET(DBT2_DRIVER bin/dbt2-driver) SET(DBT2_TXN_TEST bin/dbt2-transaction-test) +set(CMAKE_EXE_LINKER_FLAGS -Wl,--no-as-needed) # # Check for large file support by using 'getconf'. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] bgworker crashed or not?
On 04/17/2014 08:35 AM, Craig Ringer wrote: On 04/17/2014 04:47 AM, Petr Jelinek wrote: Well the logging is just too spammy in general when it comes to dynamic bgworkers but that's easy to fix in the future, no need to make decisions for 9.4. Agreed - it's the *API* that we need sorted out for 9.4, and log output isn't something Pg tends to consider part of the API. However I really don't like that I have to exit with exit code 1, which is normally used as failure, if I want to shutdown my dynamic bgworker once it has finished the work. And this behavior is something we can set properly only once... As far as I can tell we have a couple of options: - Redefine what the exit codes mean so that exit 0 suppresses auto-restart and exits silently. Probably simplest. I'm now strongly in favour of this alternative. I've just noticed that the bgworker control interfaces do not honour bgw.bgw_restart_time = BGW_NEVER_RESTART if you exit with status zero. This means that it's not simply a problem where you can't say restart me if I crash, but not if I exit normally. You also can't even say never restart me at all. Because BGW_NEVER_RESTART seems to really mean BGW_NO_RESTART_ON_CRASH. This _needs_fixing before 9.4. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers