[HACKERS] PostgreSQL Process memory architecture
Hi, I have a question regarding the memory consumption per process in PostgreSQL 9.2 Does each PostgreSQL process allocating in its own memory (Not shared memory) a cache of all the database catalog which it access during the SQL execution? I mean does each process holds all the catalog indexes data which it accessed, all the catalog index statistics etc' accessed If yes is there a way to avoid this behavior? (I asked Josh Berkus from PGExperts and he said that each process holds memory for sorts, hashes, temp tables, vaccum, etc') Thanks, Lior
Re: [HACKERS] PostgreSQL Process memory architecture
> Does each PostgreSQL process allocating in its own memory (Not shared > memory) a cache of all the database catalog which it access during the SQL > execution? > > I mean does each process holds all the catalog indexes data which it > accessed, all the catalog index statistics etc’ accessed AFAIK, the shared disk buffers are the only part shared between the processes. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On Mon, May 27, 2013 at 2:01 PM, Craig Ringer wrote: > On 05/25/2013 05:39 PM, Simon Riggs wrote: > - Switching to single-major-version release numbering. The number of > people who say "PostgreSQL 9.x" is amazing; even *packagers* get this > wrong and produce "postgresql-9" packages. Witness Amazon Linux's awful > PostgreSQL packages for example. Going to PostgreSQL 10.0, 11.0, 12.0, > etc with a typical major/minor scheme might be worth considering. > In this case you don't even need the 2nd digit... Btw, -1 for the idea, as it would remove the possibility to tell that a new major release incrementing the 1st digit of version number brings more enhancement than normal major releases incrementing the 1st digit. This was the case for 9.0, helping people in remembering that streaming replication has been introduced from 9.x series. -- Michael
[HACKERS] repeated warnings with 9.3 Beta 1 on windows
Hello, while playing with 9.3 Beta 1 on windows, I've found following small issue: create table t as select 'a' from generate_series (1,20) the warning is returned more than once: WARNUNG: Spalte "?column?" hat Typ "unknown" DETAIL: Relation wird trotzdem erzeugt. WARNUNG: Spalte "?column?" hat Typ "unknown" DETAIL: Relation wird trotzdem erzeugt. WARNUNG: Spalte "?column?" hat Typ "unknown" DETAIL: Relation wird trotzdem erzeugt. ... WARNUNG: Spalte "?column?" hat Typ "unknown" DETAIL: Relation wird trotzdem erzeugt. WARNUNG: Spalte "?column?" hat Typ "unknown" DETAIL: Relation wird trotzdem erzeugt. WARNUNG: Spalte "?column?" hat Typ "unknown" DETAIL: Relation wird trotzdem erzeugt. Abfrage war erfolgreich durchgeführt: 20 Zeilen, 312 ms Ausführungszeit. same test on 9.1 Linux: WARNING: column "a" has type "unknown" DETAIL: Proceeding with relation creation anyway. Query returned successfully: 2000 rows affected, 9266 ms execution time. regards, Marc Mamin
Re: [HACKERS] PostgreSQL Process memory architecture
On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote: > Hi Atri, > > Thanks for your answer! > Do you have idea what may be the reason that PostgreSQL process consume more > memory when there are more partial indexes on the DB table? Well, I am not too sure, but indexes always take up more space, so if your backend has a lot of indexes, it will cause the process to consume more memory. Indexes should be used with care, as too many indexes can cause a memory overhead,which can cause performance degradations. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Thanks Atri! Do you know why PostgreSQL store the indexes in memory per process and not in the shared memory? Is there a way to prevent it store the indexes data per process, and force it storing it in the shared memory? Lior -Original Message- From: Atri Sharma [mailto:atri.j...@gmail.com] Sent: Monday, May 27, 2013 13:19 To: Ben Zeev, Lior; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote: > Hi Atri, > > Thanks for your answer! > Do you have idea what may be the reason that PostgreSQL process consume more > memory when there are more partial indexes on the DB table? Well, I am not too sure, but indexes always take up more space, so if your backend has a lot of indexes, it will cause the process to consume more memory. Indexes should be used with care, as too many indexes can cause a memory overhead,which can cause performance degradations. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] COPY .... (FORMAT binary) syntax doesn't work
On 26 May 2013 16:35, Heikki Linnakangas wrote:
>> My attempts to fix that look pretty ugly, so I'm not even going to
>> post them. I can stop the error on binary by causing errors on csv and
>> text, obviously not a fix. Any grammar based fix looks like it would
>> restrict the list of formats, which breaks the orginal intention of
>> the syntax change.
>
>
> This seems to work:
This was almost exactly the fix I described above that only fixes that
specific case and then breaks others.
> --- a/src/backend/parser/gram.y
> +++ b/src/backend/parser/gram.y
> @@ -2528,3 +2528,7 @@ copy_generic_opt_elem:
> {
> $$ = makeDefElem($1, $2);
> }
> + | ColLabel BINARY
> + {
> + $$ = makeDefElem($1, (Node *)
> makeString("binary"));
> + }
So, no that doesn't work.
--
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] [BUGS] COPY .... (FORMAT binary) syntax doesn't work
On 26 May 2013 17:10, Tom Lane wrote: > Heikki Linnakangas writes: > More readable would be to invent an intermediate nonterminal falling > between ColId and ColLabel, whose expansion would be "IDENT | > unreserved_keyword | col_name_keyword | type_func_name_keyword", and > then replace ColId_or_Sconst with whatever-we-call-that_or_Sconst. > Any thoughts about a name for that new nonterminal? Do you think complicating the parser in that way is worth the trouble for this case? Could that slow down parsing? We don't actually have to fix it; clearly not too many people are bothered, since no complaints in 3 years. Documenting 'binary' seems better. -- 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] repeated warnings with 9.3 Beta 1 on windows
On Monday, May 27, 2013 3:07 PM Marc Mamin wrote: > Hello, > while playing with 9.3 Beta 1 on windows, I've found following small issue: > create table t as select 'a' from generate_series (1,20) > the warning is returned more than once: > WARNUNG: Spalte "?column?" hat Typ "unknown" > DETAIL: Relation wird trotzdem erzeugt. > WARNUNG: Spalte "?column?" hat Typ "unknown" > DETAIL: Relation wird trotzdem erzeugt. .. I have tried with latest code of PG 9.3 on Windows and I get below output which is right: postgres=# create table tbl as select 'a' from generate_series(1,20); WARNING: column "?column?" has type "unknown" DETAIL: Proceeding with relation creation anyway. SELECT 20 I am not sure why the command tag at end of command (Query returned successfully: ...) is different for you, are you doing something extra than below steps 1. initdb 2. start server 3. connect with psql 4. run the command : create table t as select 'a' from generate_series (1,20); With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
On Mon, May 27, 2013 at 3:55 PM, Ben Zeev, Lior wrote: > Thanks Atri! > > Do you know why PostgreSQL store the indexes in memory per process and not in > the shared memory? > Is there a way to prevent it store the indexes data per process, and force it > storing it in the shared memory? Ok, sorry for a bit of a confusion here. I am assuming that the multiple processes are accessing the same database. What happens essentially is that each index is stored as a separate file in the data directory of the database in the directory of the cluster in which your database belongs. So,indexes are essentially stored the same way as tables, in form of files which are accessed in 8K blocks. If your index is big/you have too many indexes in your database, it should affect *all* backends accessing that specific database. So,my point is that,there is no question of indexes being stored in shared memory or individually. You can treat indexes the same as your tables,from the point of view of physical storage. For more details,you can see http://www.postgresql.org/docs/8.1/static/storage.html Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
On Mon, May 27, 2013 at 7:25 PM, Ben Zeev, Lior wrote: > Thanks Atri! > > Do you know why PostgreSQL store the indexes in memory per process and not in > the shared memory? > Is there a way to prevent it store the indexes data per process, and force it > storing it in the shared memory? > An index is built in backend process's local memory, but, when accessing, index pages are stored in shared memory. That is, for example, when an index scan is performed, index pages are brought into shared memory and accessed from there. -- Amit Langote -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
> An index is built in backend process's local memory, but, when > accessing, index pages are stored in shared memory. That is, for > example, when an index scan is performed, index pages are brought into > shared memory and accessed from there. > > Yes, brought into the shared disk buffers and read,just like tables are read. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On Sun, May 26, 2013 at 09:18:41PM -0400, Stephen Frost wrote: > * Josh Berkus (j...@agliodbs.com) wrote: > > and it's entirely possible that we'll be able to implement SMs without > > breaking pgupgrade. > > I'd certainly hope so.. It's certainly not obvious, to me at least, > why a new SM or supporting any of those features would require > breaking pg_upgrade. Perhaps there's something I'm not seeing there, > but it had better be a *really* good reason.. If I had to _guess_, I would say users who are using the default storage manager would still be able to use pg_upgrade, and those using non-default storage managers perhaps can't. But, again, this is all so hypothetical that it doesn't seem worth talking about. My big point is that someone came to me at PGCon asking if I knew anything about why Simon thought we needed to break pg_upgrade in <2 years, and I said no, so I had go digging into my email to find out what was going on. Simon has a very visible position in the community, so when he suggests something, people take it seriously, which means I have to address it. I would prefer if there was more thought put into the ideas before they are posted. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.j...@gmail.com) wrote: > > Does each PostgreSQL process allocating in its own memory (Not shared > > memory) a cache of all the database catalog which it access during the SQL > > execution? This information is pulled into a backend-local cache, but it should only be cached while it's needed and then purged out to allow for new data coming in. It would be great if we could understand what the issue is that you're seeing. > > I mean does each process holds all the catalog indexes data which it > > accessed, all the catalog index statistics etc’ accessed Each backend shouldn't try to hold all the data, if there is pressure for that memory. > AFAIK, the shared disk buffers are the only part shared between the processes. There's a bit of other information shared, but disk buffers are certainly the bulk of it. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.j...@gmail.com) wrote: > On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote: > > Do you have idea what may be the reason that PostgreSQL process consume > > more memory when there are more partial indexes on the DB table? It might use a bit more, but it shouldn't be excessive.. What, exactly, are you seeing and would it be possible for you to provide a repeatable test case with a small-ish set of data? > Well, I am not too sure, but indexes always take up more space, so if > your backend has a lot of indexes, it will cause the process to > consume more memory. Indexes require additional disk space, certainly. Having a lot of indexes, by itself, shouldn't seriously increase memory usage. > Indexes should be used with care, as too many indexes can cause a > memory overhead,which can cause performance degradations. This is not generally a reason to avoid indexes. Indexes require more disk space and must be kept up to date, making them expensive to maintain due to increased disk i/o. Building an index uses as much memory as it's allowed to- it uses maintenance_work_mem to limit itself. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
>> AFAIK, the shared disk buffers are the only part shared between the >> processes. > > There's a bit of other information shared, but disk buffers are > certainly the bulk of it. The other information being locks? Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Do you know why PostgreSQL store the indexes in memory per process and not in > the shared memory? The disk blocks from an index are not stored per-process, they are kept in shared memory. When building an index, PG can only use one process and so there isn't any point having that be in shared memory. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Does each PostgreSQL process allocating in its own memory (Not shared memory) > a cache of all the database catalog which it access during the SQL execution? PG will look up and cache the catalog information regarding all of the relations involved, yes. In *accessing* those relations, PG will pull needed blocks into shared buffers. PG will use backend-local memory to process through the data (generally on a per-tuple basis). > I mean does each process holds all the catalog indexes data which it > accessed, all the catalog index statistics etc' accessed Catalog information (eg: information in pg_class) is kept, but the *data* will only be pulled through shared buffers and then processed. Anything in shared buffers (eg: the data in the tables or indexes) will be cleaned up as new blocks are needed which push out old ones. > If yes is there a way to avoid this behavior? Catalog information is only cached- if the information isn't being used then it should get purged out in favor of new data which is needed. Can you explain a bit more exactly what the issue is..? > (I asked Josh Berkus from PGExperts and he said that each process holds > memory for sorts, hashes, temp tables, vaccum, etc') Correct, most backend local usage of memory is for running queries and doing what is required in those queries. Regarding temp tables, you can control how much memory is used for those with the temp_buffers parameter. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.j...@gmail.com) wrote: > If your index is big/you have too many indexes in your database, it > should affect *all* backends accessing that specific database. More indexes will require more disk space, certainly, but tablespaces can be used to seperate databases, or tables, or indexes on to different partitions on the host server. > So,my point is that,there is no question of indexes being stored in > shared memory or individually. You can treat indexes the same as your > tables,from the point of view of physical storage. Correct. > For more details,you can see > > http://www.postgresql.org/docs/8.1/static/storage.html A better place to look would be the documentation for the release of PG which you are on, or the latest release otherwise, which is: http://www.postgresql.org/docs/9.2/static/storage.html Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
> A better place to look would be the documentation for the release of PG > which you are on, or the latest release otherwise, which is: > > http://www.postgresql.org/docs/9.2/static/storage.html Oops,yes,sorry about that. Thanks a ton for pointing that out. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
> This is not generally a reason to avoid indexes. Indexes require more > disk space and must be kept up to date, making them expensive to > maintain due to increased disk i/o. Building an index uses as much > memory as it's allowed to- it uses maintenance_work_mem to limit itself. Yes, too many indexes wont hurt much.BTW,wont making too many indexes on columns that probably dont have as many values as to deserve them(so,essentially,indiscriminately making indexes) hurt the performance/memory usage? Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
On Mon, May 27, 2013 at 9:16 PM, Atri Sharma wrote: >>> AFAIK, the shared disk buffers are the only part shared between the >>> processes. >> >> There's a bit of other information shared, but disk buffers are >> certainly the bulk of it. > > The other information being locks? CreateSharedMemoryAndSemaphores() (src/backend/storage/ipc/ipci.c) seems to be the place where we can see what all things reside in shared memory, since at the beginning of the function, you can see size being computed for shared memory to hold all the things that need to be in shared memory. -- Amit Langote -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
* Bruce Momjian (br...@momjian.us) wrote: > If I had to _guess_, I would say users who are using the default storage > manager would still be able to use pg_upgrade, and those using > non-default storage managers perhaps can't. That would make sense. > But, again, this is all so hypothetical that it doesn't seem worth > talking about. Having a specific list of "these are the things we want to change, and why, and here is why pg_upgrade can't support it" would be much more useful to work from, I agree. That said, many discussions and ideas do get shut down, perhaps too early, because of pg_upgrade considerations. If we had a plan to have an incompatible release in the future, those ideas and discussions might be able to progress to a point where we determine it's worth it to take the pain of a non-pg_upgrade-supported release. That's a bit of a stretch, in my view, but I suppose it's possible. Even so though, I would suggest that we put together a wiki page to list out those items and encourage people to add to such a list; perhaps having an item on that list would make discussion about it progress beyond "it breaks pg_upgrade". Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.j...@gmail.com) wrote: > > There's a bit of other information shared, but disk buffers are > > certainly the bulk of it. > > The other information being locks? Depends, but yes. Per-row locks are actually in the disk cache portion of shared buffers, but heavyweight locks have their own area. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.j...@gmail.com) wrote: > Yes, too many indexes wont hurt much.BTW,wont making too many indexes > on columns that probably dont have as many values as to deserve > them(so,essentially,indiscriminately making indexes) hurt the > performance/memory usage? I'd expect the performance issue would be from planner time more than memory usage- but if there is a serious memory usage issue here, then it'd be valuable to have a test case showing what's happening. We may not be releasing the sys cache in some cases or otherwise have a bug in this area. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
> I'd expect the performance issue would be from planner time more than > memory usage- but if there is a serious memory usage issue here, then > it'd be valuable to have a test case showing what's happening. We may > not be releasing the sys cache in some cases or otherwise have a bug in > this area. Right, this does sound interesting. Thanks a ton! Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Hi Stephen, The case which I'm seeing is that I have an empty table without any rows, Create table test ( Num Integer, C1 character varying(512), C2 character varying(512), C3 character varying(512)); I create several partial indexes on this table: Create index(index_1_c1) on test(c1) where Num=1; Create index(index_2_c1) on test(c1) where Num=2; Create index(index_1_c2) on test(c1) where Num=1; Create index(index_2_c2) on test(c1) where Num=2; ... This doesn't consume much memory on the PostgreSQL backend process, But if I create 500 indexes It consume several MB of memory. If I have 10 tables with 500 indexes each PostgreSql backend process consume 20MB, If I have 100 tables with 500 indexes each PostgreSQL backend process consume 200MB All tables are empty without data. If have Connection pool of 100 connections then All this processes consume 100*200MB = 20GB of memory What is the reason to consume so much memory for empty indexes? Thanks, Lior -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Monday, May 27, 2013 15:16 To: Atri Sharma Cc: Ben Zeev, Lior; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture * Atri Sharma (atri.j...@gmail.com) wrote: > On Mon, May 27, 2013 at 3:41 PM, Ben Zeev, Lior wrote: > > Do you have idea what may be the reason that PostgreSQL process consume > > more memory when there are more partial indexes on the DB table? It might use a bit more, but it shouldn't be excessive.. What, exactly, are you seeing and would it be possible for you to provide a repeatable test case with a small-ish set of data? > Well, I am not too sure, but indexes always take up more space, so if > your backend has a lot of indexes, it will cause the process to > consume more memory. Indexes require additional disk space, certainly. Having a lot of indexes, by itself, shouldn't seriously increase memory usage. > Indexes should be used with care, as too many indexes can cause a > memory overhead,which can cause performance degradations. This is not generally a reason to avoid indexes. Indexes require more disk space and must be kept up to date, making them expensive to maintain due to increased disk i/o. Building an index uses as much memory as it's allowed to- it uses maintenance_work_mem to limit itself. Thanks, Stephen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
On Mon, May 27, 2013 at 6:02 PM, Ben Zeev, Lior wrote: > Hi Stephen, > > The case which I'm seeing is that I have an empty table without any rows, > Create table test ( > Num Integer, > C1 character varying(512), > C2 character varying(512), > C3 character varying(512)); > > I create several partial indexes on this table: > > Create index(index_1_c1) on test(c1) where Num=1; > Create index(index_2_c1) on test(c1) where Num=2; > Create index(index_1_c2) on test(c1) where Num=1; > Create index(index_2_c2) on test(c1) where Num=2; It is just a hunch, but all of your attributes are character varying. Could TOAST be an issue here? Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Hi Atri, But TOAST only occur if the tuple size exceed 2KB, doesn't it? Lior -Original Message- From: Atri Sharma [mailto:atri.j...@gmail.com] Sent: Monday, May 27, 2013 15:39 To: Ben Zeev, Lior Cc: Stephen Frost; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture On Mon, May 27, 2013 at 6:02 PM, Ben Zeev, Lior wrote: > Hi Stephen, > > The case which I'm seeing is that I have an empty table without any > rows, Create table test ( > Num Integer, > C1 character varying(512), > C2 character varying(512), > C3 character varying(512)); > > I create several partial indexes on this table: > > Create index(index_1_c1) on test(c1) where Num=1; Create > index(index_2_c1) on test(c1) where Num=2; Create index(index_1_c2) on > test(c1) where Num=1; Create index(index_2_c2) on test(c1) where > Num=2; It is just a hunch, but all of your attributes are character varying. Could TOAST be an issue here? Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > The case which I'm seeing is that I have an empty table without any rows, > Create table test ( > Num Integer, > C1 character varying(512), > C2 character varying(512), > C3 character varying(512)); > > I create several partial indexes on this table: > > Create index(index_1_c1) on test(c1) where Num=1; > Create index(index_2_c1) on test(c1) where Num=2; > Create index(index_1_c2) on test(c1) where Num=1; > Create index(index_2_c2) on test(c1) where Num=2; > ... > > This doesn't consume much memory on the PostgreSQL backend process, > But if I create 500 indexes It consume several MB of memory. When are you seeing this memory utilization..? When running a query against that table? At backend start? > If I have 10 tables with 500 indexes each PostgreSql backend process consume > 20MB, > If I have 100 tables with 500 indexes each PostgreSQL backend process consume > 200MB > > All tables are empty without data. Are you accessing all of those tables inside of one query? Or one transaction, or..? > What is the reason to consume so much memory for empty indexes? I'm curious what you would expect to be happening here. We need to pull in information about the index in order to consider it during planning. Special-caseing empty indexes might be possible, but what's the point of having hundreds of empty indexes against a table in the first place? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
* Atri Sharma (atri.j...@gmail.com) wrote: > It is just a hunch, but all of your attributes are character varying. > Could TOAST be an issue here? TOAST tables are only created when needed. In addition, I believe Lior's concerned about memory utilization and not disk usage; memory utilization should not be impacted by TOAST at all unless large values in the tables (which had to be moved to a TOAST table due to size) are actually being queried against. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
On 05/27/2013 01:25 PM, Ben Zeev, Lior wrote: > Thanks Atri! > > Do you know why PostgreSQL store the indexes in memory per process and not in > the shared memory? >From shared_buffers point of view tables and indexes are identical, both use the same shared memory in (usually) 8KB pages > Is there a way to prevent it store the indexes data per process, and force it > storing it in the shared memory? It already does. Per-query sorts and hashtables are stored in local memory, ordinary tables and indexes are in shared. -- 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] PostgreSQL Process memory architecture
Hi Stephen, Yes, The memory utilization per PostgreSQL backend process is when running queries against this tables, For example: select * from test where num=2 and c2='abc' When It start it doesn't consume to much memory, But as it execute against more and more indexes the memory consumption grows This tables should contain data, But I truncate the data of the tables because I wanted to make sure that the memory consumption is not relate to the data inside the table, but rather to the structure of the tables Thanks, Lior -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Monday, May 27, 2013 15:43 To: Ben Zeev, Lior Cc: Atri Sharma; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > The case which I'm seeing is that I have an empty table without any > rows, Create table test ( > Num Integer, > C1 character varying(512), > C2 character varying(512), > C3 character varying(512)); > > I create several partial indexes on this table: > > Create index(index_1_c1) on test(c1) where Num=1; Create > index(index_2_c1) on test(c1) where Num=2; Create index(index_1_c2) on > test(c1) where Num=1; Create index(index_2_c2) on test(c1) where > Num=2; ... > > This doesn't consume much memory on the PostgreSQL backend process, > But if I create 500 indexes It consume several MB of memory. When are you seeing this memory utilization..? When running a query against that table? At backend start? > If I have 10 tables with 500 indexes each PostgreSql backend process > consume 20MB, If I have 100 tables with 500 indexes each PostgreSQL > backend process consume 200MB > > All tables are empty without data. Are you accessing all of those tables inside of one query? Or one transaction, or..? > What is the reason to consume so much memory for empty indexes? I'm curious what you would expect to be happening here. We need to pull in information about the index in order to consider it during planning. Special-caseing empty indexes might be possible, but what's the point of having hundreds of empty indexes against a table in the first place? Thanks, Stephen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Yes, The memory utilization per PostgreSQL backend process is when running > queries against this tables, > For example: select * from test where num=2 and c2='abc' > When It start it doesn't consume to much memory, > But as it execute against more and more indexes the memory consumption grows Are these all running in one transaction, or is this usage growth across multiple transactions? If this is all in the same transaction, what happens when you do these queries in independent transactions? > This tables should contain data, But I truncate the data of the tables > because I wanted to make sure that the memory consumption is not relate to > the data inside the table, but rather to the structure of the tables If you actually have sufficient data to make having 500 indexes on a table sensible, it strikes me that this memory utilization may not be the biggest issue you run into. If you're looking for partitioning, that's much better done, in PG at least, by using inheiritance and constraint exclusion. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] Unsigned integer types
Hi all
I know this topic was discussed before, but there doesn't seem to be
any conclusion.
The lack of unsigned integer types is one of the biggest sources of
grief in my daily work with pgsql.
Before I go and start hacking, I'd like to discuss few points:
1. Is there a strong objection against merging this kind of patch?
I can provide numerous reasons why using bigger int or numeric type
just doesn't cut.
2. How/if should the behaviour of numeric literals change?
The minimalistic solution is: it shouldn't, literals should be assumed
signed by default. More complex solution could involve using C-style
suffix ('123456u').
3. How/if should comparing singed and unsigned types work?
IMO they shouldn't be allowed and explicit cast should be required.
Thanks in advance!
Maciek
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On Mon, May 27, 2013 at 08:26:48AM -0400, Stephen Frost wrote: > * Bruce Momjian (br...@momjian.us) wrote: > > If I had to _guess_, I would say users who are using the default storage > > manager would still be able to use pg_upgrade, and those using > > non-default storage managers perhaps can't. > > That would make sense. > > > But, again, this is all so hypothetical that it doesn't seem worth > > talking about. > > Having a specific list of "these are the things we want to change, and > why, and here is why pg_upgrade can't support it" would be much more > useful to work from, I agree. > > That said, many discussions and ideas do get shut down, perhaps too > early, because of pg_upgrade considerations. If we had a plan to have > an incompatible release in the future, those ideas and discussions might > be able to progress to a point where we determine it's worth it to take > the pain of a non-pg_upgrade-supported release. That's a bit of a > stretch, in my view, but I suppose it's possible. Even so though, I > would suggest that we put together a wiki page to list out those items > and encourage people to add to such a list; perhaps having an item on > that list would make discussion about it progress beyond "it breaks > pg_upgrade". Yes, we should be collecting things we want to do for a pg_upgrade break so we can see the list all in one place. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 05/26/2013 06:18 PM, Josh Berkus wrote: >> Not sure which ones Simon meant, but at least any new/better >> storage manager would seem to me to be requiring >> a non-pg_upgrade upgrade path unless we require the storage manager >> to also include a parallel implementation of pg_upgrade. > Isn't this a bit of horse-cart inversion here? We just hashed out a > tentative, incomplete pseudo-spec for storage managers *yesterday*. Many people have been *thinking* about pluggable storage / storage managers for much longer time. > We > don't have a complete spec at this point, let alone a development plan, I think we will have a development plan *before* complete spec anyway :) > and it's entirely possible that we'll be able to implement SMs without > breaking pgupgrade. My point was exactly to not spend majority of new storage manager discussion on "does it break pg_upgrade", "maybe we can find a way to do it without breaking pg_upgrade", etc... > It's also not at all clear that we can develop SMs in less than 2 years. > I tend to think it unlikely. I think the important part of Simons message was not "two years" > First, let's have a few features for which breaking binary compatibility > is a necessity or a clear benefit. Then we'll schedule when to break them. But rather than "it breaks pg_upgrade" not being a complete stopper for proposed useful features that might break it. -- 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] PostgreSQL Process memory architecture
Hi Stephen, Each query is running in a separate transaction. Why does portioning is done better rather than using partial index? Thanks, Lior -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Monday, May 27, 2013 16:15 To: Ben Zeev, Lior Cc: Atri Sharma; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Yes, The memory utilization per PostgreSQL backend process is when > running queries against this tables, For example: select * from test where > num=2 and c2='abc' > When It start it doesn't consume to much memory, But as it execute > against more and more indexes the memory consumption grows Are these all running in one transaction, or is this usage growth across multiple transactions? If this is all in the same transaction, what happens when you do these queries in independent transactions? > This tables should contain data, But I truncate the data of the tables > because I wanted to make sure that the memory consumption is not > relate to the data inside the table, but rather to the structure of > the tables If you actually have sufficient data to make having 500 indexes on a table sensible, it strikes me that this memory utilization may not be the biggest issue you run into. If you're looking for partitioning, that's much better done, in PG at least, by using inheiritance and constraint exclusion. Thanks, Stephen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Yes, The memory utilization per PostgreSQL backend process is when running > queries against this tables, > For example: select * from test where num=2 and c2='abc' > When It start it doesn't consume to much memory, > But as it execute against more and more indexes the memory consumption grows It might be interesting, if possible for you, to recompile PG with -DCATCACHE_FORCE_RELEASE, which should cause PG to immediately release cached information when it's no longer being used. You'll be trading memory usage for CPU cycles, of course, but it might be better for your situation. We may still be able to do better than what we're doing today, but I'm still suspicious that you're going to run into other issues with having 500 indexes on a table anyway. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] PostgreSQL Process memory architecture
Great, Thanks !!! I will try and let you update -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Monday, May 27, 2013 16:29 To: Ben Zeev, Lior Cc: Atri Sharma; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture Lior, * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Yes, The memory utilization per PostgreSQL backend process is when > running queries against this tables, For example: select * from test where > num=2 and c2='abc' > When It start it doesn't consume to much memory, But as it execute > against more and more indexes the memory consumption grows It might be interesting, if possible for you, to recompile PG with -DCATCACHE_FORCE_RELEASE, which should cause PG to immediately release cached information when it's no longer being used. You'll be trading memory usage for CPU cycles, of course, but it might be better for your situation. We may still be able to do better than what we're doing today, but I'm still suspicious that you're going to run into other issues with having 500 indexes on a table anyway. Thanks, Stephen -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
* Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: > Each query is running in a separate transaction. Interesting. You might also compile with CATCACHE_STATS (and not CATCACHE_FORCE_RELEASE, or perhaps with and without) and then check out your logs after the process ends (you might need to increase the logging level to DEBUG2 if you don't see anything initially). > Why does portioning is done better rather than using partial index? There's a couple of reasons, but for one thing, you can do parallel loading of data into partitioned tables (particularly if you refer to the individual partitions directly rather than going through the top-level table with a trigger or similar). Trying to parallel load into one table with 500 indexes would be pretty painful, I expect. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Unsigned integer types
Maciej Gajewski wrote: > I know this topic was discussed before, but there doesn't seem to be > any conclusion. > > The lack of unsigned integer types is one of the biggest sources of > grief in my daily work with pgsql. > > Before I go and start hacking, I'd like to discuss few points: > > 1. Is there a strong objection against merging this kind of patch? > > I can provide numerous reasons why using bigger int or numeric type > just doesn't cut. It would be interesting to know these reasons. 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] Unsigned integer types
Maciej Gajewski writes:
> The lack of unsigned integer types is one of the biggest sources of
> grief in my daily work with pgsql.
> Before I go and start hacking, I'd like to discuss few points:
> 1. Is there a strong objection against merging this kind of patch?
Basically, there is zero chance this will happen unless you can find
a way of fitting them into the numeric promotion hierarchy that doesn't
break a lot of existing applications. We have looked at this more than
once, if memory serves, and failed to come up with a workable design
that didn't seem to violate the POLA.
> 2. How/if should the behaviour of numeric literals change?
> The minimalistic solution is: it shouldn't, literals should be assumed
> signed by default. More complex solution could involve using C-style
> suffix ('123456u').
Well, if you don't do that, there is no need for you to merge anything:
you can build unsigned types as an external extension if they aren't
affecting the core parser's behavior. As long as it's external, you
don't need to satisfy anybody else's idea of what reasonable behavior
is ...
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] PostgreSQL Process memory architecture
>We may still be able to do better than what we're doing > today, but I'm still suspicious that you're going to run into other > issues with having 500 indexes on a table anyway. +1. I am suspicious that the large number of indexes is the problem here,even if the problem is not with book keeping associated with those indexes. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] repeated warnings with 9.3 Beta 1 on windows
Marc Mamin writes: > while playing with 9.3 Beta 1 on windows, I've found following small issue: > create table t as select 'a' from generate_series (1,20) > the warning is returned more than once: I can't duplicate this either. > Abfrage war erfolgreich durchgeführt: 20 Zeilen, 312 ms > Ausführungszeit. That doesn't look like anything psql would print, which makes me think that you're using some other client application and it's that application that's misbehaving. 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] Processing long AND/OR lists
On Mon, May 27, 2013 at 1:42 AM, Gurjeet Singh wrote: > > >> Joking about "640K" aside, it doesn't seem reasonable to expect a truly >> enormous query as is generated by the broken forms of this logic to turn >> out happily. I'd rather fix Slony (as done in the above patch). >> > > Yes, by all means, fix the application, but that doesn't preclude the > argument that the database should be a bit more smarter and efficient, > especially if it is easy to do. Agreed, it seems like a fine idea to have the database support such queries, as this eases coping with applications that might be more difficult to get fixed. (I can't see too many users generating such enormous queries by hand! :-)) -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?"
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
Bruce Momjian writes: > On Mon, May 27, 2013 at 08:26:48AM -0400, Stephen Frost wrote: >> That said, many discussions and ideas do get shut down, perhaps too >> early, because of pg_upgrade considerations. If we had a plan to have >> an incompatible release in the future, those ideas and discussions might >> be able to progress to a point where we determine it's worth it to take >> the pain of a non-pg_upgrade-supported release. That's a bit of a >> stretch, in my view, but I suppose it's possible. Even so though, I >> would suggest that we put together a wiki page to list out those items >> and encourage people to add to such a list; perhaps having an item on >> that list would make discussion about it progress beyond "it breaks >> pg_upgrade". > Yes, we should be collecting things we want to do for a pg_upgrade break > so we can see the list all in one place. Precisely. We've said right along that we reserve the right to have a non-upgradable disk format change whenever sufficiently many reasons accumulate to do that. The way to go about that is to collect projects that need to be kept on hold for such a release --- not to say we're going to have such a release and then look for reasons. 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] [BUGS] COPY .... (FORMAT binary) syntax doesn't work
Simon Riggs writes: > On 26 May 2013 17:10, Tom Lane wrote: >> More readable would be to invent an intermediate nonterminal falling >> between ColId and ColLabel, whose expansion would be "IDENT | >> unreserved_keyword | col_name_keyword | type_func_name_keyword", and >> then replace ColId_or_Sconst with whatever-we-call-that_or_Sconst. >> Any thoughts about a name for that new nonterminal? > Do you think complicating the parser in that way is worth the trouble > for this case? Could that slow down parsing? It makes the grammar tables a bit larger (1% or so IIRC). There would be some distributed penalty for that, but probably not much. Of course there's always the slippery-slope argument about that. > We don't actually have to fix it; clearly not too many people are > bothered, since no complaints in 3 years. Documenting 'binary' seems > better. Well, my thought is there are other cases. For instance: regression=# create role binary; ERROR: syntax error at or near "binary" LINE 1: create role binary; ^ regression=# create user cross; ERROR: syntax error at or near "cross" LINE 1: create user cross; ^ If we don't have to treat type_func_name_keywords as reserved in these situations, shouldn't we avoid doing so? 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
[HACKERS] New committers
It's been brought to my attention that I forgot to email hackers about us adding new committers to the project, as I planned to do when I wrote my blog post about it. This is the same people as were announced during the pgcon closing session - Jeff Davis, Stephen frost, fujii masao and Noah misch. My apologies for forgetting to announce it here, and another welcome to the team to our new guys! /Magnus
Re: [HACKERS] View Index and UNION
Hi Tom You are right: UNION ALL is correct in terms of contents (tables contents are disjunct) and of performance (no separate sort required theoretically). In my specific case even with UNION ALL the planner still chose a "Seq Scan". Note that there is a KNN index with "ORDER BY ... <-> ..." involved. I have to dig into my tests in order to give you the EXPLAIN ANALYZE. Yours, Stefan 2013/5/26 Tom Lane : > Stefan Keller writes: >> Given following schema: > >> 1. TABLE a and TABLE b, each with INDEX on attribute geom. > >> 2. A VIEW with union: > >> CREATE VIEW myview AS >> SELECT * FROM a >> UNION >> SELECT * FROM b; > >> 3. And a simple query with KNN index and a coordinate "mypos" : > >> SELECT * FROM myview >> ORDER BY ST_Geomfromtext(mypos) <-> myview.geom > > I think this would work out-of-the-box in 9.1 or later, if you > made the view use UNION ALL instead of UNION. > > 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] Planning incompatibilities for Postgres 10.0
Michael Paquier escribió: > On Mon, May 27, 2013 at 2:01 PM, Craig Ringer wrote: > > > On 05/25/2013 05:39 PM, Simon Riggs wrote: > > - Switching to single-major-version release numbering. The number of > > people who say "PostgreSQL 9.x" is amazing; even *packagers* get this > > wrong and produce "postgresql-9" packages. Witness Amazon Linux's awful > > PostgreSQL packages for example. Going to PostgreSQL 10.0, 11.0, 12.0, > > etc with a typical major/minor scheme might be worth considering. > > > In this case you don't even need the 2nd digit... You do -- they are used for minor releases, i.e. 10.1 would be a bugfix release for 10.0. If we continue using the current numbering scheme, 10.1 would be the major version after 10.0. > Btw, -1 for the idea, as it would remove the possibility to tell that a new > major release incrementing the 1st digit of version number brings more > enhancement than normal major releases incrementing the 1st digit. This was > the case for 9.0, helping people in remembering that streaming replication > has been introduced from 9.x series. All major releases bring lots of enhancements. Streaming replication might be great for some people, but I'm sure there are things in 8.4 and 9.1 that are equally great for some other people. -- Á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] Planning incompatibilities for Postgres 10.0
Tom Lane wrote: > Bruce Momjian writes: > > Yes, we should be collecting things we want to do for a pg_upgrade break > > so we can see the list all in one place. > > Precisely. We've said right along that we reserve the right to have a > non-upgradable disk format change whenever sufficiently many reasons > accumulate to do that. Do we have a wiki page about this? -- Á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] Planning incompatibilities for Postgres 10.0
On Mon, May 27, 2013 at 09:17:50AM -0400, Bruce Momjian wrote: > > That said, many discussions and ideas do get shut down, perhaps too > > early, because of pg_upgrade considerations. If we had a plan to have > > an incompatible release in the future, those ideas and discussions might > > be able to progress to a point where we determine it's worth it to take > > the pain of a non-pg_upgrade-supported release. That's a bit of a > > stretch, in my view, but I suppose it's possible. Even so though, I > > would suggest that we put together a wiki page to list out those items > > and encourage people to add to such a list; perhaps having an item on > > that list would make discussion about it progress beyond "it breaks > > pg_upgrade". > > Yes, we should be collecting things we want to do for a pg_upgrade break > so we can see the list all in one place. OK, I have added a section to the TODO list for this: Desired changes that would prevent upgrades with pg_upgrade 32-bit page checksums Are there any others? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 27 May 2013 15:36, Tom Lane wrote: > Bruce Momjian writes: >> On Mon, May 27, 2013 at 08:26:48AM -0400, Stephen Frost wrote: >>> That said, many discussions and ideas do get shut down, perhaps too >>> early, because of pg_upgrade considerations. If we had a plan to have >>> an incompatible release in the future, those ideas and discussions might >>> be able to progress to a point where we determine it's worth it to take >>> the pain of a non-pg_upgrade-supported release. That's a bit of a >>> stretch, in my view, but I suppose it's possible. Even so though, I >>> would suggest that we put together a wiki page to list out those items >>> and encourage people to add to such a list; perhaps having an item on >>> that list would make discussion about it progress beyond "it breaks >>> pg_upgrade". > >> Yes, we should be collecting things we want to do for a pg_upgrade break >> so we can see the list all in one place. > > Precisely. We've said right along that we reserve the right to have a > non-upgradable disk format change whenever sufficiently many reasons > accumulate to do that. I'm happy with that. I was also thinking about collecting changes not related just to disk format, if any exist. > The way to go about that is to collect projects > that need to be kept on hold for such a release --- not to say we're > going to have such a release and then look for reasons. Agreed. I was trying to establish a realistic timeline for such events, so that the planning was able to be taken seriously. Yes, it wass a "work backwards" or "what if" type of planning. But now we have a rough plan of how it might look, collecting ideas can begin. -- 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] repeated warnings with 9.3 Beta 1 on windows
> which makes me think that you're using some other client application Hello, Tom is right ( as always :-) : http://postgresql.1045698.n5.nabble.com/bug-repeated-messages-in-pgadmin-1-18-0-Alpha-1-query-tool-messages-pane-td5755749.html sorry for the disturbance. Marc Maminm Von: Tom Lane [t...@sss.pgh.pa.us] Gesendet: Montag, 27. Mai 2013 16:25 An: Marc Mamin Cc: 'pgsql-hackers@postgresql.org' Betreff: Re: [HACKERS] repeated warnings with 9.3 Beta 1 on windows Marc Mamin writes: > while playing with 9.3 Beta 1 on windows, I've found following small issue: > create table t as select 'a' from generate_series (1,20) > the warning is returned more than once: I can't duplicate this either. > Abfrage war erfolgreich durchgeführt: 20 Zeilen, 312 ms > Ausführungszeit. That doesn't look like anything psql would print, which makes me think that you're using some other client application and it's that application that's misbehaving. 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] Planning incompatibilities for Postgres 10.0
Bruce Momjian writes: > On Mon, May 27, 2013 at 09:17:50AM -0400, Bruce Momjian wrote: >> Yes, we should be collecting things we want to do for a pg_upgrade break >> so we can see the list all in one place. > OK, I have added a section to the TODO list for this: > Desired changes that would prevent upgrades with pg_upgrade > 32-bit page checksums > Are there any others? GiST indexes really oughta have a metapage so there can be a version number in them. Also, if we are going to unify hstore and json, it'd be nice if we could change the existing binary representation of hstore (per discussions at Oleg and Teodor's talk --- this will be moot if we invent a new core type, but it'd be better not to have to). There are probably some other data-type-specific cleanups we could make, but I have to go get on an airplane so no time to think about 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] Planning incompatibilities for Postgres 10.0
Bruce Momjian wrote: > OK, I have added a section to the TODO list for this: > > Desired changes that would prevent upgrades with pg_upgrade > > 32-bit page checksums > > Are there any others? I would have each data segment be self-identifying, i.e. have a magic number at the beginning of the file and the relation OID, some fork identification and the segment number somewhere -- probably the special space of the first page. -- Á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] New committers
On Mon, May 27, 2013 at 10:32 AM, Magnus Hagander wrote: > It's been brought to my attention that I forgot to email hackers about us > adding new committers to the project, as I planned to do when I wrote my > blog post about it. > > This is the same people as were announced during the pgcon closing session - > Jeff Davis, Stephen frost, fujii masao and Noah misch. > Congratulations for you guys... -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación Phone: +593 4 5107566 Cell: +593 987171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On Tue, May 28, 2013 at 12:36 AM, Alvaro Herrera wrote: > Michael Paquier escribió: > > On Mon, May 27, 2013 at 2:01 PM, Craig Ringer > wrote: > > > > > On 05/25/2013 05:39 PM, Simon Riggs wrote: > > > - Switching to single-major-version release numbering. The number of > > > people who say "PostgreSQL 9.x" is amazing; even *packagers* get this > > > wrong and produce "postgresql-9" packages. Witness Amazon Linux's awful > > > PostgreSQL packages for example. Going to PostgreSQL 10.0, 11.0, 12.0, > > > etc with a typical major/minor scheme might be worth considering. > > > > > In this case you don't even need the 2nd digit... > > You do -- they are used for minor releases, i.e. 10.1 would be a bugfix > release for 10.0. If we continue using the current numbering scheme, > 10.1 would be the major version after 10.0. > Sorry for the confusion. I meant that the 2nd digit would not be necessary when identifying a given major release, so I just didn't get the meaning of what Craig said. As you say, you would still need the 2nd digit for minor releases. -- Michael
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On Tue, May 28, 2013 at 07:39:35AM +0900, Michael Paquier wrote: > On Tue, May 28, 2013 at 12:36 AM, Alvaro Herrera > wrote: > > > Michael Paquier escribió: > > > On Mon, May 27, 2013 at 2:01 PM, Craig Ringer > > wrote: > > > > > > > On 05/25/2013 05:39 PM, Simon Riggs wrote: > > > > - Switching to single-major-version release numbering. The number of > > > > people who say "PostgreSQL 9.x" is amazing; even *packagers* get this > > > > wrong and produce "postgresql-9" packages. Witness Amazon Linux's awful > > > > PostgreSQL packages for example. Going to PostgreSQL 10.0, 11.0, 12.0, > > > > etc with a typical major/minor scheme might be worth considering. > > > > > > > In this case you don't even need the 2nd digit... > > > > You do -- they are used for minor releases, i.e. 10.1 would be a bugfix > > release for 10.0. If we continue using the current numbering scheme, > > 10.1 would be the major version after 10.0. > > > Sorry for the confusion. I meant that the 2nd digit would not be necessary > when identifying a given major release, so I just didn't get the meaning of > what Craig said. As you say, you would still need the 2nd digit for minor > releases. What's been proposed before that wouldn't break previous applications is a numbering system like this: 10.0.0 10.0.1 10.0.2 10.0.3 ... 11.0.0 11.0.1 i.e. only change the "most-major" version number and always leave the "less-major" number as zero. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
Michael Paquier escribió: > On Tue, May 28, 2013 at 12:36 AM, Alvaro Herrera > wrote: > > You do -- they are used for minor releases, i.e. 10.1 would be a bugfix > > release for 10.0. If we continue using the current numbering scheme, > > 10.1 would be the major version after 10.0. > > > Sorry for the confusion. I meant that the 2nd digit would not be necessary > when identifying a given major release, so I just didn't get the meaning of > what Craig said. As you say, you would still need the 2nd digit for minor > releases. Well, that seems okay to me. We used to see a lot of people talking about "Postgres 8.x" when they meant, say, 8.3; and now we have people talking about "Postgres 9" when in reality they mean 9.1 or some other specific major version. Having the second digit be part of the major version number is a difficult idea to convey to external people. -- Á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] adding import in pl/python function
On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote: > Well, it's easy. > > Instead of PLyFloat_FromNumeric[0], you can make a > PLyDecimal_FromNumeric. Please send a patch. This would be a welcome addition. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On Tue, May 28, 2013 at 7:52 AM, David Fetter wrote: > What's been proposed before that wouldn't break previous applications > is a numbering system like this: > > 10.0.0 > 10.0.1 > 10.0.2 > 10.0.3 > ... > 11.0.0 > 11.0.1 > > i.e. only change the "most-major" version number and always leave the > "less-major" number as zero. > Thanks for the clarification. Firefox did exactly the same from 4.0. -- Michael
Re: [HACKERS] adding import in pl/python function
On Mon, May 27, 2013 at 8:13 PM, Peter Eisentraut wrote: > On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote: >> Well, it's easy. >> >> Instead of PLyFloat_FromNumeric[0], you can make a >> PLyDecimal_FromNumeric. > > Please send a patch. This would be a welcome addition. I can write it blind as I have more than enough experience with CPython, but I don't use PLPython so I can't perform extensive testing. If someone's willing to do the testing, by all means. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 05/27/2013 05:45 PM, Michael Paquier wrote: > On Mon, May 27, 2013 at 2:01 PM, Craig Ringer wrote: > >> On 05/25/2013 05:39 PM, Simon Riggs wrote: >> - Switching to single-major-version release numbering. The number of >> people who say "PostgreSQL 9.x" is amazing; even *packagers* get this >> wrong and produce "postgresql-9" packages. Witness Amazon Linux's awful >> PostgreSQL packages for example. Going to PostgreSQL 10.0, 11.0, 12.0, >> etc with a typical major/minor scheme might be worth considering. >> > In this case you don't even need the 2nd digit... > Btw, -1 for the idea, as it would remove the possibility to tell that a new > major release incrementing the 1st digit of version number brings more > enhancement than normal major releases incrementing the 1st digit. This was > the case for 9.0, helping people in remembering that streaming replication > has been introduced from 9.x series. I don't find bumping the major to be particularly helpful. Every release brings major features - and some introduce major incompatibilities. 8.4 introduced CTEs. 8.3 broke tons of client code with the removal of implicit casts to text. It really depends on what features you consider more major/significant. Personally I don't think it makes sense to try to say "this release is bigger" in Pg - at least not in terms of enhancement. I can see value in using this-release-is-bigger for "this brings more breakage" - but would strongly prefer a smooth and continuous release numbering that doesn't confuse the heck out of users. I'm extremely tired of being told "I'm running PostgreSQL 8.x" or "I'm running PostgreSQL 9.x" and having to point out the version policy, the fact that there are four years and huge fixes/enhancements between 8.0 and 8.4, etc. The version policy makes _no distinction_ between which digit changes in a major release: "PostgreSQL major releases include new features and occur roughly once every year. A major release is numbered by increasing either the first or second part of the version number, e.g. 8.2 to 8.3. "Major releases usually change the internal format of system tables and data files. These changes are often complex, so we do not maintain backward compatibility of all stored data. A dump/reload of the database or use of the pg_upgrade module is required for major upgrades." and I strongly believe that we should drop the notion entirely. ... -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 05/28/2013 07:22 AM, Michael Paquier wrote: > On Tue, May 28, 2013 at 7:52 AM, David Fetter wrote: > >> What's been proposed before that wouldn't break previous applications >> is a numbering system like this: >> >> 10.0.0 >> 10.0.1 >> 10.0.2 >> 10.0.3 >> ... >> 11.0.0 >> 11.0.1 >> >> i.e. only change the "most-major" version number and always leave the >> "less-major" number as zero. >> > Thanks for the clarification. Firefox did exactly the same from 4.0. Yeah... I was more meaning 10.0, 10.1, 10.2 etc for minor releases, but I can imagine people coding logic to check "major version" using the first two digits, so you're quite right that it'd need to be grandfathered into 10.0.1, 10.0.2, etc. Sigh. The upside of that is that it'd reinforce the idea that we sometimes struggle to get across to people - that minor patch releases are *minor* and *safe* to just upgrade to without jumping through change-approval hoops, vendor approval for updates, two-year-long QA and all the other baggage many IT departments seem to have. -- 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] adding import in pl/python function
On Mon, 2013-05-27 at 20:43 -0300, Claudio Freire wrote: > On Mon, May 27, 2013 at 8:13 PM, Peter Eisentraut wrote: > > On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote: > >> Well, it's easy. > >> > >> Instead of PLyFloat_FromNumeric[0], you can make a > >> PLyDecimal_FromNumeric. > > > > Please send a patch. This would be a welcome addition. > > > I can write it blind as I have more than enough experience with > CPython, but I don't use PLPython so I can't perform extensive > testing. > If someone's willing to do the testing, by all means. Yes please. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 05/28/2013 12:41 AM, Simon Riggs wrote: > I'm happy with that. > > I was also thinking about collecting changes not related just to disk > format, if any exist. Any wire protocol or syntax changes? I can't seem to find a "things we want to do in wire protocol v4" doc in the wiki but I know I've seen occasional discussion of things that can't be done without protocol changes. Anyone with a better memory than me able to pitch in? What'd be required to support in-band query cancellation? Sending per-statement GUCs (to allow true statement timeout)? I can't think of any major syntax warts and grandfathered quirks that'd be really great to get rid of if we had the freedom to break things. -- 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] pgbench --startup option
On 02/11/2013 07:27 AM, Jeff Janes wrote: > I created doBenchMarkConnect() to segregate bench-marking connections from > utility connections. At first I thought of adding the startup code to only > the normal path and leaving support for -C in the wind, but decided that > was just lazy. That sounds very useful and would've eased some recent pgbench work I've been doing too. I've put some patches together to make pgbench capable of talking to multiple servers. I needed it for benchmarking work on bidirectional replication, but it's also useful if you want to benchmark a group of hot standbys in read-only mode, and it may be useful with various 3rd pty replication solutions. As written it uses one or more threads per server, with all clients managed by a given thread using the same server. Multiple servers are specified by using connstring style syntax, eg: pgbench -T 600 -j 4 -c 64 "host=server1 user=postgres" "host=server2 user=postgres port=5433" It isn't ready for a commitfest yet as I need to tidy up a few things and I still haven't added an extra set of timings to measure how long the DB takes to return to a steady state after the pgbench run, but once that's done I'll send it in. The after-run timings are intended for things like measuring how much lag an asynchronous replica has built up and how long it takes to catch up after the write flood stops, or how long a CHECKPOINT takes after the pgbench run. I also have a patch that adds a flag to force a CHECKPOINT after vacuum and before running its tests. This makes pgbench results _vastly_ more stable over short runs. The work is currently lurking in the 'multibench' branch of git://github.com/ringerc/postgres.git ; see https://github.com/ringerc/postgres/tree/multibench. Only pgbench.c is actually changed. Comments appreciated. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Re: [HACKERS] high io BUT huge amount of free memory
On 05/03/2013 07:09 AM, Andres Freund wrote: > We've got that in 9.3 which is absolutely fabulous! But that's not > related to doing DMA which you cannot (and should not!) do from > userspace. You can do zero-copy DMA directly into userspace buffers. It requires root (or suitable capabilities that land up equivalent to root anyway) and requires driver support, and it's often a terrible idea, but it's possible. It's used by a lot of embedded systems, by infiniband, and (if I vaguely recall correctly) by things like video4linux drivers. You can use get_user_pages and set the write flag. Linux Device Drivers chapter 15 discusses it. That said, I think some of the earlier parts of this discussion confused direct asynchronous I/O with DMA. Within-kernel DMA may be (ok, is) used to implement DIO, but that doesn't mean you're DMA'ing directly into userspace buffers. -- 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] Planning incompatibilities for Postgres 10.0
On 05/27/2013 04:58 PM, Craig Ringer wrote: On 05/28/2013 12:41 AM, Simon Riggs wrote: I'm happy with that. I was also thinking about collecting changes not related just to disk format, if any exist. Any wire protocol or syntax changes? I can't seem to find a "things we want to do in wire protocol v4" doc in the wiki but I know I've seen occasional discussion of things that can't be done without protocol changes. Anyone with a better memory than me able to pitch in? What'd be required to support in-band query cancellation? Sending per-statement GUCs (to allow true statement timeout)? I would like to see the ability to define if a query is read only at the protocol level, so that load balances that speak libpq can know what to do with the query without parsing it. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 509-416-6579 PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC, @cmdpromptinc For my dreams of your image that blossoms a rose in the deeps of my heart. - W.B. Yeats -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 28/05/13 11:48, Craig Ringer wrote: On 05/27/2013 05:45 PM, Michael Paquier wrote: On Mon, May 27, 2013 at 2:01 PM, Craig Ringer wrote: On 05/25/2013 05:39 PM, Simon Riggs wrote: - Switching to single-major-version release numbering. The number of people who say "PostgreSQL 9.x" is amazing; even *packagers* get this wrong and produce "postgresql-9" packages. Witness Amazon Linux's awful PostgreSQL packages for example. Going to PostgreSQL 10.0, 11.0, 12.0, etc with a typical major/minor scheme might be worth considering. In this case you don't even need the 2nd digit... Btw, -1 for the idea, as it would remove the possibility to tell that a new major release incrementing the 1st digit of version number brings more enhancement than normal major releases incrementing the 1st digit. This was the case for 9.0, helping people in remembering that streaming replication has been introduced from 9.x series. I don't find bumping the major to be particularly helpful. Every release brings major features - and some introduce major incompatibilities. 8.4 introduced CTEs. 8.3 broke tons of client code with the removal of implicit casts to text. It really depends on what features you consider more major/significant. Personally I don't think it makes sense to try to say "this release is bigger" in Pg - at least not in terms of enhancement. I can see value in using this-release-is-bigger for "this brings more breakage" - but would strongly prefer a smooth and continuous release numbering that doesn't confuse the heck out of users. I'm extremely tired of being told "I'm running PostgreSQL 8.x" or "I'm running PostgreSQL 9.x" and having to point out the version policy, the fact that there are four years and huge fixes/enhancements between 8.0 and 8.4, etc. The version policy makes _no distinction_ between which digit changes in a major release: "PostgreSQL major releases include new features and occur roughly once every year. A major release is numbered by increasing either the first or second part of the version number, e.g. 8.2 to 8.3. "Major releases usually change the internal format of system tables and data files. These changes are often complex, so we do not maintain backward compatibility of all stored data. A dump/reload of the database or use of the pg_upgrade module is required for major upgrades." and I strongly believe that we should drop the notion entirely. ... -- Craig Ringerhttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services Yes, I hate the Firefox style number inflation. Cheers, Gavin
Re: [HACKERS] Planning incompatibilities for Postgres 10.0
On 05/28/2013 09:39 AM, Gavin Flower wrote: > Yes, I hate the Firefox style number inflation. I was arguing *for* it ;-) I don't like it much either, but (a) we do about one release a year, not one every few weeks and (b) it's very clear from a quick look at Stack Overflow or first-posts to pgsql-general how confusing two-part major versions are to users. If it's a bit less aesthetically pleasing I'm OK with that. -- 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] [PATCH] add --throttle to pgbench (submission 3)
On 05/02/2013 12:56 AM, Greg Smith wrote: > On 5/1/13 4:57 AM, Fabien COELHO wrote: >> The use case of the option is to be able to generate a continuous gentle >> load for functional tests, eg in a practice session with students or for >> testing features on a laptop. > > If you add this to > https://commitfest.postgresql.org/action/commitfest_view?id=18 I'll > review it next month. I have a lot of use cases for a pgbench that > doesn't just run at 100% all the time. As do I - in particular, if time permits I'll merge this patch into my working copy of pgbench so I can find the steady-state transaction rate where BDR replication's lag is stable and doesn't increase continually. Right now I don't really have any way of doing that, only measuring how long it takes to catch up once the test run completes. -- 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] Planning incompatibilities for Postgres 10.0
On 05/27/2013 06:53 PM, Craig Ringer wrote: On 05/28/2013 09:39 AM, Gavin Flower wrote: Yes, I hate the Firefox style number inflation. I was arguing *for* it ;-) I don't like it much either, but (a) we do about one release a year, not one every few weeks and (b) it's very clear from a quick look at Stack Overflow or first-posts to pgsql-general how confusing two-part major versions are to users. If it's a bit less aesthetically pleasing I'm OK with that. This argument comes up every couple of years and the people that are trying to solve the problem by changing the versioning are ignoring the fact that there is no problem to solve. Consider the following exchange: Client: I have X problem with PostgreSQL CMD: What version? Client: 9 CMD: Which version of 9? Client: 9.0.2 CMD: You should be running 9.2.4 or at least 9.0.13 Now, if we change the version numbers: Client: I have X problem with PostgreSQL CMD: What version? Client: 9 CMD: Which version of 9? Client: 9.0.2 CMD: You should be running 10.0.5 or at least 9.0.13 The conversation does not change. Further, we are not Firefox. We are not user software. We are developer software. Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] commit fest schedule for 9.4
On 05/16/2013 01:44 AM, Josh Berkus wrote: > I'll also say: > * we need to assign CF managers at least 2 weeks in advance of each CF * > we need to replace them if they get too busy to follow-through, > * and the last CF needs two managers. Strong +1 on both of those. I tried to pick up a CF that was already totally off the rails most of the way through, then had a bunch of other work come in. Add inexperience with the process and, well, it didn't go well. I see nothing but advantages in having more than one person involved. A decent lead-in rather than trying to step in once it's already in progress and badly behind would make a world of difference too. Finally, I'd like to see the CFs can be kept short enough and closed on schedule ready-or-not with all open work bumped to the next CF, so that people doing the CF as part of their work can schedule their participation properly rather than having it open-ended and uncertain. This would make it a lot easier to get a firm commitment of dedicated time. -- 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] Extent Locks
On 05/17/2013 11:38 AM, Robert Haas wrote: > maybe with a bit of modest pre-extension. When it comes to pre-extension, is it realistic to get a count of backends waiting on the lock and extend the relation by (say) 2x the number of waiting backends? Getting a list of lock waiters is always a racey proposition, but in this case we don't need an accurate count, only an estimate, and the count can only grow between getting the count and completing the relation extension. Assuming it's even remotely feasible to get a count of lock waiters at all. If there are lots of procs waiting to extend the relation a fair chunk could be preallocated with posix_fallocate on supported platforms. If it's possible this would avoid the need to attempt any recency-of-last-extension based preallocation with the associated problem of how to store and access the last-extended time efficiently, while still hopefully reducing contention on the relation extension lock and without delaying the backend doing the extension too much more. -- 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] Extent Locks
On 05/18/2013 03:15 AM, Josh Berkus wrote: > The drawback to this is whatever size we choose is liable to be wrong > for some users. Users who currently have a lot of 16K tables would see > their databases grow alarmingly. This only becomes a problem for tables that're tiny, right? If your table is already 20MB you don't care if it grows to 20.1MB or 21MB next time it's extended. What about applying the relation extent size only *after* an extent's worth of blocks have been allocated in small blocks, per current behaviour? So their 32k tables stay 32k, but once they step over the 1MB barrier (or whatever) in table size the allocation mode switches to bulk-allocating large extents? Or just setting an size threshold after which extent-sized preallocation is used? -- 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] Logging of PAM Authentication Failure
On 05/11/2013 03:25 AM, Robert Haas wrote: > Not really. We could potentially fix it by extending the wire > protocol to allow the server to respond to the client's startup packet > with a further challenge, and extend libpq to report that challenge > back to the user and allow sending a response. But that would break > on-the-wire compatibility, which we haven't done in a good 10 years, > and certainly wouldn't be worthwhile just for this. We were just talking about "things we'd like to do in wire protocol 4". Allowing multi-stage authentication has come up repeatedly and should perhaps go on that list. The most obvious case being "ident auth failed, demand md5". -- 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] adding import in pl/python function
On 28 May 2013 01:55, Peter Eisentraut wrote: > On Mon, 2013-05-27 at 20:43 -0300, Claudio Freire wrote: > > On Mon, May 27, 2013 at 8:13 PM, Peter Eisentraut > wrote: > > > On Fri, 2013-05-24 at 16:46 -0300, Claudio Freire wrote: > > >> Well, it's easy. > > >> > > >> Instead of PLyFloat_FromNumeric[0], you can make a > > >> PLyDecimal_FromNumeric. > > > > > > Please send a patch. This would be a welcome addition. > > > > > > I can write it blind as I have more than enough experience with > > CPython, but I don't use PLPython so I can't perform extensive > > testing. > > If someone's willing to do the testing, by all means. > > Yes please. > > I'm working on that. - Szymon
Re: [HACKERS] Logging of PAM Authentication Failure
On 5/27/13, Craig Ringer wrote: > We were just talking about "things we'd like to do in wire protocol 4". > > Allowing multi-stage authentication has come up repeatedly and should > perhaps go on that list. The most obvious case being "ident auth failed, > demand md5". I'd like to use LDAP with pg_ident Cheers, Jeff -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
