[HACKERS] Fwd: proposal GSoC 2015 task: Allow access to the database via HTTP
Hi Team. I would like to solve a problem of Allow access to the database via HTTP. But before drawing up the demand in GSOC I wanted to consult here. Therefore I will be grateful to comments from attendees here! 1. I think, will better use access to DB through the stand-alone program which not necessarily has to be on the same server. At least because it will give certain freedom in cluster systems. 2. Whether it is obligatory to use a programming language C for this purpose? After all as the stand-alone program ( frontend ) it has to be not necessarily written in the same programming language as the server ( backend ). I would prefer to use the python language for writing as I consider that this language is more clear to system administrators + to bring much more simply editings in a code. 3. What you will advise what to pass a selection stage in GSOC 2015 from postgresql?) PS: my English is poor. I ask you to forgive me for it. Best Regards, Vadim Gorbachov
Re: [HACKERS] Table-level log_autovacuum_min_duration
Michael Paquier wrote: In AutoVacWorkerMain, I am reading the following: * Currently, we don't pay attention to postgresql.conf changes that * happen during a single daemon iteration, so we can ignore SIGHUP. */ pqsignal(SIGHUP, SIG_IGN); So a worker does not see changes in postgresql.conf once it is run and processes a database, no? The launcher does run ProcessConfigFile() when SIGHUP shows up though. Maybe this is something that we should change. For example, I wonder if this can also affect the cost-delay balancing heuristics; if two backends run the rebalance with different GUC settings because postgresql.conf changed in between each of them starting, would the settings bounce back and forth. I think it's worth reconsidering this. (Don't really remember in detail how it works; maybe it's fine now.) In any case, for log_autovacuum_min_duration it also seems worth keeping reasonably close track of GUC changes. I think reading them just before starting vacuum of a new relation should be enough. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Exposing PG_VERSION_NUM in pg_config
Hi all,
When working on extensions or packaging, one can do some grep-ing on
pg_config.h to get PG_VERSION_NUM to do version-related operations. An
example of that is the Makefile of plv8 using --include-dir with perl
and a regex:
https://github.com/plv8/plv8/blob/master/Makefile
Wouldn't it be more simple to expose PG_VERSION_NUM in pg_config with
a new option? Like that for example:
$ pg_config --version-num
90500
Attached is a patch implementing the idea.
Regards,
--
Michael
diff --git a/doc/src/sgml/ref/pg_config-ref.sgml b/doc/src/sgml/ref/pg_config-ref.sgml
index 0210f63..6297f02 100644
--- a/doc/src/sgml/ref/pg_config-ref.sgml
+++ b/doc/src/sgml/ref/pg_config-ref.sgml
@@ -275,6 +275,15 @@
/varlistentry
varlistentry
+ termoption--version-num/option/
+ listitem
+ para
+ Print the version of productnamePostgreSQL/ as a number.
+ /para
+ /listitem
+/varlistentry
+
+varlistentry
termoption-?//term
termoption--help//term
listitem
diff --git a/src/bin/pg_config/pg_config.c b/src/bin/pg_config/pg_config.c
index 9b1f95d..8b261d4 100644
--- a/src/bin/pg_config/pg_config.c
+++ b/src/bin/pg_config/pg_config.c
@@ -382,6 +382,14 @@ show_version(bool all)
printf(PostgreSQL PG_VERSION \n);
}
+static void
+show_version_num(bool all)
+{
+ if (all)
+ printf(VERSION_NUM = );
+ printf(%d\n, PG_VERSION_NUM);
+}
+
/*
* Table of known information items
@@ -418,6 +426,7 @@ static const InfoItem info_items[] = {
{--ldflags_sl, show_ldflags_sl},
{--libs, show_libs},
{--version, show_version},
+ {--version-num, show_version_num},
{NULL, NULL}
};
@@ -454,6 +463,7 @@ help(void)
printf(_( --ldflags_sl show LDFLAGS_SL value used when PostgreSQL was built\n));
printf(_( --libsshow LIBS value used when PostgreSQL was built\n));
printf(_( --version show the PostgreSQL version\n));
+ printf(_( --version-num show the PostgreSQL version as a number\n));
printf(_( -?, --helpshow this help, then exit\n));
printf(_(\nWith no arguments, all known items are shown.\n\n));
printf(_(Report bugs to pgsql-bugs@postgresql.org.\n));
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SSL renegotiation
On 02/23/2015 04:01 PM, Albe Laurenz wrote: I think you could remove renegotiation from PostgreSQL as long as you offer something better than RC4 in the TLS handshake. I'd say it is best to wait if and how OpenSSL change their API when they implement TLS 1.3. I'd vote against removing renegotiation. I'm just suggesting that the effort required to fix bugs in this part of PostgreSQL could be spent better elsewhere. If changing the encryption is so useless, whe did the TLS workgroup decide to introduce rekeying as a substitute for renegotiation? Theoretical considerations, mostly. If rekeying is strictly required after processing just a few petabytes, the cipher is severely broken and should no longer be used. -- Florian Weimer / Red Hat Product Security -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Superuser connect during smart shutdown
Jim Nasby jim.na...@bluetreble.com wrote: On 3/20/15 9:44 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian br...@momjian.us wrote: OK, are we up for changing the default pg_ctl shutdown method for 9.5, (smart to fast), [...]? I'm up for it. I think it's long overdue. +1 +1, but I also like the idea of allowing SU to connect during a smart shutdown. Even if you've intentionally chosen smart instead of fast it still sucks that you can't find out what's actually holding things up (and ps isn't that great a solution). I like that idea a lot, too. Having been in the position of remotely administering about 80 database servers, and getting a call that the building containing one of them was on fire, and the fire department would be arriving in two or three minutes to cut power to the building and start spraying water on everything, I found current behavior rather nervous-making as I struggled to get a clean shutdown of PostgreSQL followed by a clean shutdown and power-off of the server before that happened. The ability to make an SU connection during either fast or smart shutdown can be useful in a world of connection pools and long-running report queries. And fires. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table-level log_autovacuum_min_duration
On Mon, Mar 23, 2015 at 7:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Michael Paquier wrote: So a worker does not see changes in postgresql.conf once it is run and processes a database, no? The launcher does run ProcessConfigFile() when SIGHUP shows up though. Maybe this is something that we should change. Yeah, checking for SIGHUP in the worker outer loop (ie once per table) seems like a reasonable thing. regards, tom lane Could it be done more often? Maybe every time it is about to do a cost_delay sleep? I've certainly regretted the inability to change autovacuum_vacuum_cost_delay mid-table on more than one occasion. This was mostly during doing testing work, but still I'm sure other people have run into this problem, perhaps without knowing it. Cheers, Jeff
Re: [HACKERS] Display of multi-target-table Modify plan nodes in EXPLAIN
On Mon, Mar 23, 2015 at 10:26 AM, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas hlinn...@iki.fi writes: On 03/22/2015 03:02 AM, Tom Lane wrote: In a green field we might choose to solve this by refactoring the output so that it's logically ... but I think that ship has sailed. Changing the logical structure of EXPLAIN output like this would break clients that know what's where in JSON/YAML/XML formats, which is exactly what we said we wouldn't do with those output formats. If we have promised that, I think we should break the promise. No application should depend on the details of EXPLAIN output, even if it's in JSON/YAML/XML format. I think this is entirely wrong. The entire point of having those machine-readable output formats was to let people write tools that would process plans in some intelligent manner. Relocating where child plans of a Modify appear in the data structure would certainly break any tool that had any understanding of plan trees. Now, maybe there are no such tools, but in that case the whole exercise in adding those formats was a waste of time and we should rip them out. In any case, what I was suggesting here is only very marginally cleaner than what got implemented, so it really doesn't seem to me to be worth breaking backwards compatibility here, even if I bought the premise that backwards compatibility of this output is of low priority. I agree that we shouldn't break backward compatibility here for no particularly good reason, but I also think it would be fine to break it if the new output were a significant improvement. People who write tools that parse this output should, and I think do, understand that sometimes we'll make changes upstream and they'll need to adjust for it. We shouldn't do that on a whim, but we shouldn't let it stand in the way of progress, either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] barnacle (running CLOBBER_CACHE_RECURSIVELY) seems stuck since November
On Sun, Mar 22, 2015 at 6:26 PM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: This time I've however checked the log, and what caught my eye is that the last log message is from November. There were regular messages until then (a few messages per day), but since Nov 19 there's nothing. I'd try strace on the backends, to see if they are issuing any system calls. I'd also try attaching gdb and pulling a back trace, then continue, then hit ^C, and pull another backtrace, repeat about 10 times, and see if you can figure out where it's stuck. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Order of enforcement of CHECK constraints?
On Fri, Mar 20, 2015 at 4:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: On Fri, Mar 20, 2015 at 4:19 PM, Peter Geoghegan p...@heroku.com wrote: On Fri, Mar 20, 2015 at 12:15 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. What not by OID, as with indexes? Are you suggesting that this would become documented behavior? I think they should be executed in alphabetical order like triggers. Yeah. We already have a comparable, and documented, behavior for triggers, so if we're going to do anything about this I'd vote for sorting by name (or more specifically, by strcmp()). Isn't better do this to read pg_constraint in name order? - conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true, + conscan = systable_beginscan(conrel, ConstraintNameNspIndexId, true, Regards, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] recovery_min_delay casting problems lead to busy looping
On 2015-03-23 10:25:48 -0400, Robert Haas wrote: On Mon, Mar 23, 2015 at 10:18 AM, Andres Freund and...@2ndquadrant.com wrote: recoveryApplyDelay() does: TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime, secs, microsecs); if (secs = 0 microsecs = 0) break; elog(DEBUG2, recovery apply delay %ld seconds, %d milliseconds, secs, microsecs / 1000); WaitLatch(XLogCtl-recoveryWakeupLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, secs * 1000L + microsecs / 1000); The problem is that the 'microsecs = 0' comparison is done while in microsecs, but the sleeping converts to milliseconds. Which will often be 0. I've seen this cause ~15-20 iterations per loop. Annoying, but not terrible. I think we should simply make the abort condition ' microsecs / 1000 = 0'. That's a subtle violation of the documented behavior Would it be? The delay is specified on a millisecond resolution, so not waiting if below one ms doesn't seem unreasonable to me. , although there's a good chance nobody would ever care. What about just changing the WaitLatch call to say Max(secs * 1000L + microsecs / 1000, 1)? I could live with that as well. Although we at least should convert the elog(DEBUG) to log milliseconds in floating point in that case. 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] Display of multi-target-table Modify plan nodes in EXPLAIN
Heikki Linnakangas hlinn...@iki.fi writes: On 03/22/2015 03:02 AM, Tom Lane wrote: In a green field we might choose to solve this by refactoring the output so that it's logically ... but I think that ship has sailed. Changing the logical structure of EXPLAIN output like this would break clients that know what's where in JSON/YAML/XML formats, which is exactly what we said we wouldn't do with those output formats. If we have promised that, I think we should break the promise. No application should depend on the details of EXPLAIN output, even if it's in JSON/YAML/XML format. I think this is entirely wrong. The entire point of having those machine-readable output formats was to let people write tools that would process plans in some intelligent manner. Relocating where child plans of a Modify appear in the data structure would certainly break any tool that had any understanding of plan trees. Now, maybe there are no such tools, but in that case the whole exercise in adding those formats was a waste of time and we should rip them out. In any case, what I was suggesting here is only very marginally cleaner than what got implemented, so it really doesn't seem to me to be worth breaking backwards compatibility here, even if I bought the premise that backwards compatibility of this output is of low priority. 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] Display of multi-target-table Modify plan nodes in EXPLAIN
Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes: On Mon, Mar 23, 2015 at 10:51 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hm? We don't have scan nodes that read more than one table, so I'm not following your point. But in the text output you gave Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46) Update on public.pt1 Foreign Update on public.ft1 Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1 Foreign Update on public.ft2 Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1 Update on public.child3 - Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46) Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid ... etc ... For ft1 there is only Update Remote SQL. whereas for child3 you have specified the Seq Scan as well. I think you're confused by my perhaps-overly-abbreviated example. Here's the whole output for the example in HEAD: # explain update pt1 set c1=c1+1; QUERY PLAN - Update on pt1 (cost=0.00..321.05 rows=3541 width=46) Update on pt1 Foreign Update on ft1 Foreign Update on ft2 Update on child3 - Seq Scan on pt1 (cost=0.00..0.00 rows=1 width=46) - Foreign Scan on ft1 (cost=100.00..148.03 rows=1170 width=46) - Foreign Scan on ft2 (cost=100.00..148.03 rows=1170 width=46) - Seq Scan on child3 (cost=0.00..25.00 rows=1200 width=46) (9 rows) # explain verbose update pt1 set c1=c1+1; QUERY PLAN Update on public.pt1 (cost=0.00..321.05 rows=3541 width=46) Update on public.pt1 Foreign Update on public.ft1 Remote SQL: UPDATE public.ref1 SET c1 = $2 WHERE ctid = $1 Foreign Update on public.ft2 Remote SQL: UPDATE public.ref2 SET c1 = $2 WHERE ctid = $1 Update on public.child3 - Seq Scan on public.pt1 (cost=0.00..0.00 rows=1 width=46) Output: (pt1.c1 + 1), pt1.c2, pt1.c3, pt1.ctid - Foreign Scan on public.ft1 (cost=100.00..148.03 rows=1170 width=46) Output: (ft1.c1 + 1), ft1.c2, ft1.c3, ft1.ctid Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref1 FOR UPDATE - Foreign Scan on public.ft2 (cost=100.00..148.03 rows=1170 width=46) Output: (ft2.c1 + 1), ft2.c2, ft2.c3, ft2.ctid Remote SQL: SELECT c1, c2, c3, ctid FROM public.ref2 FOR UPDATE - Seq Scan on public.child3 (cost=0.00..25.00 rows=1200 width=46) Output: (child3.c1 + 1), child3.c2, child3.c3, child3.ctid (17 rows) 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] [GENERAL] [pgadmin-support] Issue with a hanging apply process on the replica db after vacuum works on primary
20 марта 2015 г., в 18:00, Vladimir Borodin r...@simply.name написал(а): 19 марта 2015 г., в 20:30, Sergey Shchukin shchukin@gmail.com mailto:shchukin@gmail.com написал(а): 17.03.2015 13:22, Sergey Shchukin пишет: 05.03.2015 11:25, Jim Nasby пишет: On 2/27/15 5:11 AM, Sergey Shchukin wrote: show max_standby_streaming_delay; max_standby_streaming_delay - 30s We both need to be more clear about which server we're talking about (master or replica). What are max_standby_streaming_delay and max_standby_archive_delay set to *on the replica*? My hope is that one or both of those is set to somewhere around 8 minutes on the replica. That would explain everything. If that's not the case then I suspect what's happening is there's something running on the replica that isn't checking for interrupts frequently enough. That would also explain it. When replication hangs, is the replication process using a lot of CPU? Or is it just sitting there? What's the process status for the replay process show? Can you get a trace of the replay process on the replica when this is happening to see where it's spending all it's time? How are you generating these log lines? Tue Feb 24 15:05:07 MSK 2015 Stream: MASTER-masterdb:79607161592048 SLAVE:79607161550576 Replay:79607160986064 :: REPLAY 592 KBytes (00:00:00.398376 seconds) Do you see the confl_* fields in pg_stat_database_conflicts on the *replica* increasing? Hi Jim, max_standby_streaming_delay and max_standby_archive_delay both are 30s on master and replica dbs I don't see any specific or heavy workload during this issue with a hanging apply process. Just a normal queries as usual. But I see an increased disk activity during the time when the apply issue is ongoing DSK | sdc | | busy 61% | read 11511 | | write 4534 | KiB/r 46 | | KiB/w 4 | MBr/s 52.78 | | MBw/s 1.88 | avq 1.45 | | avio 0.38 ms | DSK | sde | | busy 60% | read 11457 | | write 4398 | KiB/r 46 | | KiB/w 4 | MBr/s 51.97 | | MBw/s 1.83 | avq 1.47 | | avio 0.38 ms | DSK | sdd | | busy 60% | read9673 | | write 4538 | KiB/r 61 | | KiB/w 4 | MBr/s 58.24 | | MBw/s 1.88 | avq 1.47 | | avio 0.42 ms | DSK | sdj | | busy 59% | read9576 | | write 4177 | KiB/r 63 | | KiB/w 4 | MBr/s 59.30 | | MBw/s 1.75 | avq 1.48 | | avio 0.43 ms | DSK | sdh | | busy 59% | read9615 | | write 4305 | KiB/r 63 | | KiB/w 4 | MBr/s 59.23 | | MBw/s 1.80 | avq 1.48 | | avio 0.42 ms | DSK | sdf | | busy 59% | read9483 | | write 4404 | KiB/r 63 | | KiB/w 4 | MBr/s 59.11 | | MBw/s 1.83 | avq 1.47 | | avio 0.42 ms | DSK | sdi | | busy 59% | read 11273 | | write 4173 | KiB/r 46 | | KiB/w 4 | MBr/s 51.50 | | MBw/s 1.75 | avq 1.43 | | avio 0.38 ms | DSK | sdg | | busy 59% | read 11406 | | write 4297 | KiB/r 46 | | KiB/w 4 | MBr/s 51.66 | | MBw/s 1.80 | avq 1.46 | | avio 0.37 ms | Although it's not seems to be an upper IO limit. Normally disks are busy at 20-45% DSK | sde | | busy 29% | read6524 | | write 14426 | KiB/r 26 | | KiB/w 5 | MBr/s 17.08 | | MBw/s 7.78 | avq10.46 | | avio 0.14 ms | DSK | sdi | | busy 29% | read6590 | | write 14391 | KiB/r 26 | | KiB/w 5 | MBr/s 17.19 | | MBw/s 7.76 | avq 8.75 | | avio 0.14 ms | DSK | sdg | | busy 29% | read6547 | | write 14401 | KiB/r 26 | | KiB/w 5 | MBr/s 16.94 | | MBw/s 7.60 | avq 7.28 | | avio 0.14 ms | DSK | sdc | | busy 29% | read6835 | | write 14283 | KiB/r 27 | | KiB/w 5 | MBr/s 18.08 | | MBw/s 7.74 | avq 8.77 | | avio 0.14 ms | DSK | sdf | | busy 23% | read3808 | |
Re: [HACKERS] snapshot too large error when initializing logical replication (9.4)
On 2015-03-20 08:41:38 -0400, Bruce Momjian wrote: On Mon, Nov 17, 2014 at 03:52:38PM +0100, Andres Freund wrote: On 2014-11-17 11:51:54 -0300, Alvaro Herrera wrote: Andres Freund wrote: Hi, On 2014-10-25 18:09:36 -0400, Steve Singer wrote: I sometimes get the error snapshot too large from my logical replication walsender process when in response to a CREATE_REPLICATION_SLOT. Yes. That's possible if 'too much' was going on until a consistent point was reached. I think we can just use a much larger size for the array if necessary. I've attached patch for this. Could you try whether that helps? I don't have a testcase handy that reproduces the problem. You haven't pushed this, have you? No, but it's on my todo list. Uh, where are we on this? Nowhere, unfortunately. The fix I'd developed isn't actually correct. The problem is that we can only import a transaction with fewer than GetMaxSnapshotXidCount() transactions atm. The only reason it worked in Steve's case is because he probably wasn't importin gthe snapshot in the test case... 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] Table-level log_autovacuum_min_duration
Alvaro Herrera alvhe...@2ndquadrant.com writes: Michael Paquier wrote: So a worker does not see changes in postgresql.conf once it is run and processes a database, no? The launcher does run ProcessConfigFile() when SIGHUP shows up though. Maybe this is something that we should change. Yeah, checking for SIGHUP in the worker outer loop (ie once per table) seems like a reasonable thing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Order of enforcement of CHECK constraints?
Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes: I might be only one objecting here but ... On Sat, Mar 21, 2015 at 12:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: My Salesforce colleagues noticed some tests flapping as a result of table CHECK constraints not always being enforced in the same order; ie, if a tuple insertion/update violates more than one CHECK constraint, it's not deterministic which one is reported. This is evidently because relcache.c's CheckConstraintFetch() just happily loads up the constraints in whatever order it happens to find them in pg_constraint. Why is it important to report in deterministic manner? If nothing else, so as not to have regression-test failures. If it really matters, we should probably report all the failing constraints. That wouldn't in itself make the output deterministic (you'd still have to sort); and in any case that's not going to happen because it would require running each CHECK constraint in its own subtransaction. Catching errors that way is *expensive*. And there's been zero field demand for such a behavior, so I don't see us adding cycles for something no one's asked for. Sorting the check constraints during relcache load, on the other hand, is a negligible burden compared to the cost of reading pg_constraint. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: pgbench - merging transaction logs
Hi, On Sat, Mar 21, 2015 at 8:42 PM, Fabien COELHO coe...@cri.ensmp.fr wrote: Hello Didier, If fprintf takes p = 0.025 (1/40) of the time, then with 2 threads the collision probability would be about 1/40 and the delayed thread would be waiting for half this time on average, so the performance impact due to fprintf locking would be negligeable (1/80 delay occured in 1/40 cases = 1/3200 time added on the computed average, if I'm not mistaken). Yes but for a third thread (each on a physical core) it will be 1/40 + 1/40 and so on up to roughly 40/40 for 40 cores. If threads run more or less the same code with the same timing after a while they will lockstep on synchronization primitives and your collision probability will be very close to 1. I'm not sure I understand. If transaction times were really constant, then after a while the mutexes would be synchronised so as to avoid contention, i.e. the collision probability would be 0? But they aren't constant only close. It may or not show up in this case but I've noticed that often the collision rate is a lot higher than the probability would suggest, I'm not sure why, Moreover they will write to the same cache lines for every fprintf and this is very very bad even without atomic operations. We're talking of transactions that involve network messages and possibly disk IOs on the server, so some cache issues issues within pgbench would not be a priori the main performance driver. Sure but : - good measurement is hard and by adding locking in fprintf it make its timing more noisy. - it's against 'good practices' for scalable code. Trivial code can show that elapsed time for as low as four cores writing to same cache line in a loop, without locking or synchronization, is greater than the elapsed time for running these four loops sequentially on one core. If they write to different cache lines it scales linearly. Regards Didier -- Sent 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: numeric timestamp in log_line_prefix
The proposed format is much simpler to manage in a script, and if you're interested in runtime, its formatting would be less expensive than %t and %m. Maybe, but do we really need two? How about just %M? I guess Tomas put 2 formats because there was 2 time formats to begin with, but truncating/rouding if someone really wants seconds is quite easy. Also, having just one would open the door to calling it something like %u (for Unix timestamp), I guess that is okay as well. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] proposal GSoC 2015 task: Allow access to the database via HTTP
Hi Team. I would like to solve a problem of Allow access to the database via HTTP. But before drawing up the demand in GSOC I wanted to consult here. Therefore I will be grateful to comments from attendees here! 1. I think, will better use access to DB through the stand-alone program which not necessarily has to be on the same server. At least because it will give certain freedom in cluster systems. 2. Whether it is obligatory to use a programming language C for this purpose? After all as the stand-alone program ( frontend ) it has to be not necessarily written in the same programming language as the server ( backend ). I would prefer to use the python language for writing as I consider that this language is more clear to system administrators + to bring much more simply editings in a code. 3. What you will advise what to pass a selection stage in GSOC 2015 from postgresql?) PS: my English is poor. I ask you to forgive me for it. Best Regards, Vadim Gorbachov
[HACKERS] recovery_min_delay casting problems lead to busy looping
Hi, recoveryApplyDelay() does: TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime, secs, microsecs); if (secs = 0 microsecs = 0) break; elog(DEBUG2, recovery apply delay %ld seconds, %d milliseconds, secs, microsecs / 1000); WaitLatch(XLogCtl-recoveryWakeupLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, secs * 1000L + microsecs / 1000); The problem is that the 'microsecs = 0' comparison is done while in microsecs, but the sleeping converts to milliseconds. Which will often be 0. I've seen this cause ~15-20 iterations per loop. Annoying, but not terrible. I think we should simply make the abort condition ' microsecs / 1000 = 0'. 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] recovery_min_delay casting problems lead to busy looping
On Mon, Mar 23, 2015 at 10:18 AM, Andres Freund and...@2ndquadrant.com wrote: recoveryApplyDelay() does: TimestampDifference(GetCurrentTimestamp(), recoveryDelayUntilTime, secs, microsecs); if (secs = 0 microsecs = 0) break; elog(DEBUG2, recovery apply delay %ld seconds, %d milliseconds, secs, microsecs / 1000); WaitLatch(XLogCtl-recoveryWakeupLatch, WL_LATCH_SET | WL_TIMEOUT | WL_POSTMASTER_DEATH, secs * 1000L + microsecs / 1000); The problem is that the 'microsecs = 0' comparison is done while in microsecs, but the sleeping converts to milliseconds. Which will often be 0. I've seen this cause ~15-20 iterations per loop. Annoying, but not terrible. I think we should simply make the abort condition ' microsecs / 1000 = 0'. That's a subtle violation of the documented behavior, although there's a good chance nobody would ever care. What about just changing the WaitLatch call to say Max(secs * 1000L + microsecs / 1000, 1)? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Zero-padding and zero-masking fixes for to_char(float)
On Mon, Mar 23, 2015 at 12:36:25AM -0400, Tom Lane wrote:
Bruce Momjian br...@momjian.us writes:
On Sun, Mar 22, 2015 at 12:46:08PM -0400, Noah Misch wrote:
I recommend adding a configure test to use our snprintf.c replacements if
sprintf(%.*f, 65536, 999.0) gives unexpected output.
Do we really want to go to our /port snprintf just to handle 512+
digits?
I'd rather not go that direction (that is, to using a configure test).
It assumes that all copies of libc on a particular platform behave the
same, which seems like a bad bet to me. I think we'd be better off to
avoid asking libc to do anything that might not work everywhere.
On the other hand, this line of thought might end up having you
reimplement in formatting.c the same logic I put into snprintf.c
recently, which seems a bit silly.
If we can't trust libc for 512+ precision (and I don't think a startup
check is warranted), I think we should either use our internal snprintf
for to_char() in all cases, or in cases where the precision is 512+. Of
course, this hinges on the assumption that only to_char() cares about
512+ digits --- if not, a startup check seems a requirement.
However, even if we have a working snprintf, the bigger problem is
having to do rounding, e.g. this is the worst case:
SELECT to_char(float4 '5.', '9D' ||
repeat('9', 1000) || '');
What we have to do here is to round to the specified precision, i.e. we
can't just pad with zeros, which was my original approach. I am afraid
that means we have to keep the odd coding where we call snprintf with
zero decimal digits, get its length, then figure out the decimal
precision, then call snprintf again to format the string with rounding.
Then, we need to separate the mantissa from the exponent and add the
desired number of zeros and copy it into a new longer string.
So, instead of the code being cleaner, it will be even more messy, and
will duplicate some of what we already do in our port/snprintf.c.
Unfortunately, there is no way to call snprintf and tell it we want a
specific number of decimal digits _and_ pad the rest with zeros. I
think trying to do the rounding on the output string will never work
well, e.g. 9. rounded to one decimal digit is 10.0.
Yuck.
--
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] Order of enforcement of CHECK constraints?
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= fabriziome...@gmail.com writes: On Fri, Mar 20, 2015 at 4:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: We could fix it by, say, having CheckConstraintFetch() sort the constraints by name after loading them. Isn't better do this to read pg_constraint in name order? - conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true, + conscan = systable_beginscan(conrel, ConstraintNameNspIndexId, true, Surely not. That would end up having to read *all* of pg_constraint, not only the rows applicable to the current relation. We could get the index to do the work for us if we changed it from an index on conrelid to one on conrelid, conname. However, seeing that that would bloat the index by a factor of sixteen, it hardly sounds like a free fix either. I really think that a quick application of qsort is the best-performing way to do this. 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] Table-level log_autovacuum_min_duration
Jeff Janes jeff.ja...@gmail.com writes: On Mon, Mar 23, 2015 at 7:07 AM, Tom Lane t...@sss.pgh.pa.us wrote: Yeah, checking for SIGHUP in the worker outer loop (ie once per table) seems like a reasonable thing. Could it be done more often? Maybe every time it is about to do a cost_delay sleep? That sounds risky from here. Normal backends don't check it more often than once per command. 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] Superuser connect during smart shutdown
On Mon, Mar 23, 2015 at 11:02 AM, Kevin Grittner kgri...@ymail.com wrote: Jim Nasby jim.na...@bluetreble.com wrote: On 3/20/15 9:44 AM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: On Thu, Mar 19, 2015 at 10:42 PM, Bruce Momjian br...@momjian.us wrote: OK, are we up for changing the default pg_ctl shutdown method for 9.5, (smart to fast), [...]? I'm up for it. I think it's long overdue. +1 +1, but I also like the idea of allowing SU to connect during a smart shutdown. Even if you've intentionally chosen smart instead of fast it still sucks that you can't find out what's actually holding things up (and ps isn't that great a solution). I like that idea a lot, too. Having been in the position of remotely administering about 80 database servers, and getting a call that the building containing one of them was on fire, and the fire department would be arriving in two or three minutes to cut power to the building and start spraying water on everything, I found current behavior rather nervous-making as I struggled to get a clean shutdown of PostgreSQL followed by a clean shutdown and power-off of the server before that happened. The ability to make an SU connection during either fast or smart shutdown can be useful in a world of connection pools and long-running report queries. And fires. That, however, is a separate issue from changing the default shutdown mode. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] inherit support for foreign tables
On Mon, Mar 23, 2015 at 12:09 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Mar 22, 2015 at 1:57 PM, Tom Lane t...@sss.pgh.pa.us wrote: Etsuro Fujita fujita.ets...@lab.ntt.co.jp writes: [ fdw-inh-8.patch ] I've committed this with some substantial rearrangements, notably: I'm really glad this is going in! Thanks to to Shigeru Hanada and Etsuro Fujita for working on this, to you (Tom) for putting in the time to get it committed, and of course to the reviewers Ashutosh Bapat and Kyotaro Horiguchi for their time and effort. In a way, I believe we can think of this as the beginnings of a sharding story for PostgreSQL. A lot more work is needed, of course -- join and aggregate pushdown are high on my personal list -- but it's a start. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] Materialiation is slower than non-materialized
On Mon, Mar 23, 2015 at 6:01 AM, Rajeev rastogi rajeev.rast...@huawei.com wrote: The cost of re-scan of SeqScan node is considered to be same scan of SeqScan node, which always assumes that the records is fetched from disk and hence disk access cost is added (As we don’t know really how much memory will be available to cache during execution). That's a general problem not limited to materialize nodes. We might choose to do a heap-sort rather than a quick-sort, but it may turn out that the tapes we create end up in the OS buffer cache instead of on physical storage; in fact, it's probably the common case. Scans are costed using seq_page_cost and random_page_cost, but most of the time the random page cost will not be the cost of a head seek, because we'll find the data in the OS page cache. Some of the time it really will be a head seek, but we have no idea whether that will happen in any given case. The autovacuum cost delays have this problem too: a miss in shared buffers may really be a hit in the OS page cache, but we don't know. This e-mail and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! This kind of disclaimer is inappropriate on a public mailing list. Don't send confidential information to public mailing lists. You probably don't have any legal right to control what happens to it after that, regardless of what you put in your email. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump quietly ignore missing tables - is it bug?
Hi
2015-03-15 16:09 GMT+01:00 Tom Lane t...@sss.pgh.pa.us:
Pavel Stehule pavel.steh...@gmail.com writes:
other variant, I hope better than previous. We can introduce new long
option --strict. With this active option, every pattern specified by -t
option have to have identifies exactly only one table. It can be used for
any other should to exists patterns - schemas. Initial implementation
in
attachment.
I think this design is seriously broken. If I have '-t foo*' the code
should not prevent that from matching multiple tables. What would the use
case for such a restriction be?
What would make sense to me is one or both of these ideas:
* require a match for a wildcard-free -t switch
* require at least one (not exactly one) match for a wildcarded -t
switch.
attached initial implementation
Regards
Pavel
Neither of those is what you wrote, though.
If we implemented the second one of these, it would have to be controlled
by a new switch, because there are plausible use cases for wildcards that
sometimes don't match anything (not to mention backwards compatibility).
There might be a reasonable argument for the first one being the
default behavior, though; I'm not sure if we could get away with that
from a compatibility perspective.
regards, tom lane
commit c1c7d9671a751bda1918d479b81c38c538701ec1
Author: Pavel Stehule pavel.steh...@gooddata.com
Date: Mon Mar 23 17:06:39 2015 +0100
initial
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index d7506e1..47ae6b8 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -983,7 +983,8 @@ bool
processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
- const char *altnamevar, const char *visibilityrule)
+ const char *altnamevar, const char *visibilityrule,
+ bool *with_wildcards)
{
PQExpBufferData schemabuf;
PQExpBufferData namebuf;
@@ -997,6 +998,10 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
(appendPQExpBufferStr(buf, have_where ? AND : WHERE ), \
have_where = true, added_clause = true)
+#define SET_WITH_WILDCARDS(b) if (with_wildcards) *with_wildcards = b;
+
+ SET_WITH_WILDCARDS(false);
+
if (pattern == NULL)
{
/* Default: select all visible objects */
@@ -1055,11 +1060,15 @@ processSQLNamePattern(PGconn *conn, PQExpBuffer buf, const char *pattern,
{
appendPQExpBufferStr(namebuf, .*);
cp++;
+
+ SET_WITH_WILDCARDS(true);
}
else if (!inquotes ch == '?')
{
appendPQExpBufferChar(namebuf, '.');
cp++;
+
+ SET_WITH_WILDCARDS(true);
}
else if (!inquotes ch == '.')
{
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index b176746..7fb7b62 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -94,7 +94,8 @@ extern bool processSQLNamePattern(PGconn *conn, PQExpBuffer buf,
const char *pattern,
bool have_where, bool force_escape,
const char *schemavar, const char *namevar,
- const char *altnamevar, const char *visibilityrule);
+ const char *altnamevar, const char *visibilityrule,
+ bool *with_wildcards);
extern void buildShSecLabelQuery(PGconn *conn, const char *catalog_name,
uint32 objectId, PQExpBuffer sql);
extern void emitShSecLabels(PGconn *conn, PGresult *res,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f24fefa..dd1d813 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -114,6 +114,7 @@ static SimpleStringList table_exclude_patterns = {NULL, NULL};
static SimpleOidList table_exclude_oids = {NULL, NULL};
static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
+static SimpleStringList optional_tables = {NULL, NULL};
char g_opaque_type[10]; /* name for the opaque type */
@@ -132,7 +133,7 @@ static ArchiveFormat parseArchiveFormat(const char *format, ArchiveMode *mode);
static void expand_schema_name_patterns(Archive *fout,
SimpleStringList *patterns,
SimpleOidList *oids);
-static void expand_table_name_patterns(Archive *fout,
+static void expand_table_name_patterns(Archive *fout, bool check_patterns,
SimpleStringList *patterns,
SimpleOidList *oids);
static NamespaceInfo *findNamespace(Archive *fout, Oid nsoid, Oid objoid);
@@ -332,6 +333,7 @@ main(int argc, char **argv)
{section, required_argument, NULL, 5},
{serializable-deferrable, no_argument, dopt.serializable_deferrable, 1},
{snapshot, required_argument, NULL, 6},
+ {table-if-exists, required_argument, NULL, 7},
{use-set-session-authorization, no_argument, dopt.use_setsessauth, 1},
{no-security-labels, no_argument, dopt.no_security_labels, 1},
{no-synchronized-snapshots, no_argument,
Re: [HACKERS] logical column ordering
On 2015-03-23 13:01:48 -0400, Robert Haas wrote: I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. I think it fairly can be marked as returned with feedback for now? 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] logical column ordering
On 2015-03-23 14:19:50 -0300, Alvaro Herrera wrote: Andres Freund wrote: On 2015-03-23 13:01:48 -0400, Robert Haas wrote: I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. I think it fairly can be marked as returned with feedback for now? ... which means that no useful feedback was received at all in this round for this patch. (There was lots of feedback, mind you, but as far as I can see it was all on the subject of how the patch is going to be summarily rejected unless user-visible controls are offered -- and you already know my opinion on that matter.) To me the actual blocker seems to be the implementation. Which doesn't look like it's going to be ready for 9.5; there seems to be loads of work left to do. It's hard to provide non flame-bait feedback if the patch isn't ready. I'm not sure what review you'd like to see at this stage? I think your approach of concentrating on the technical parts is sane, and I'd continue going that way. 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] Abbreviated keys for Numeric
On Sat, Mar 21, 2015 at 2:58 AM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Peter I don't really buy it, either way. In what sense is a NULL value Peter ever abbreviated? It isn't. Whatever about the cost model, Peter that's the truth of the matter. There is always going to be a Peter sort of tension in any cost model, between whether or not it's Peter worth making it more sophisticated, and the extent to which Peter tweaking the model is chasing diminishing returns. Comparisons between nulls and nulls, or between nulls and non-nulls, are cheap; only comparisons between non-nulls and non-nulls can be expensive. The purpose of abbreviation is to replace expensive comparisons by cheap ones where possible, and therefore the cost model used for abbreviation should ignore nulls entirely; all that matters is the number of non-null values and the probability of saving time by abbreviating them. So if you're sorting a million rows of which 900,000 are null and 100,000 contain 50 different non-null values, then the absolute time saved (not the proportion) by doing abbreviation should be on the same order as the absolute time saved by abbreviation when sorting just the 100,000 non-null rows. But if the cost model does 1,000,000/50 and gets 20,000, and decides that's worse than my 1 in 10,000 target, I'll abort abbreviations, then you have sacrificed the time gain for no reason at all. This is what I mean by spurious. This is why the cost model must compute the fraction as 100,000/50, ignoring the null inputs, if it's going to perform anything like optimally in the presence of nulls. I think Andrew is right. Peter I also think that your explanation of the encoding schemes was Peter perfunctory. I'm interested in other opinions on that, because I find your replacement for it both confusingly organized and a bit misleading (for example saying the top bit is wasted is wrong, it's reserved because we need it free for the sign). (It is true that mine assumes that the reader knows what excess-N means, or can find out.) Here's mine, which is given as a single block comment: [ long explanatory comment ] Peter's, inline with the code (omitted here): [ long explanatory comment ] In my opinion, Andrew's version is far clearer. Peter's version is full of jargon that I can't understand. I could probably figure it out with a few hours and a search engine, but that really shouldn't be necessary. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 12:20 PM, Robert Haas robertmh...@gmail.com wrote: Guys, can we please knock it off with the dueling patches? Peter, it's really not all that helpful to take somebody else's patch, rewrite it in a form that they may or may not agree with (even if it's just the comments), and post that as v2. And when the person then posts v3 that reverts most of your changes, don't go put them all back and call that v4. Instead, you should take the hint: these are not versions of the same patch - they are two different approaches to the same problem. In this type of situation, I generally post my patch with a name like topicofthepatch-rmh-v1.patch or topicofthepatch-rmh-20150323.patch, putting my initials in there to show that this is my version of the patch, not the original author's and that it may or may not be endorsed by the original author. Having 26 versions of this patch where all of the odd-numbered versions looks like Andrew's original version and all of the even-numbered versions look like Peter's v2 is not going to make anybody happy - not either of you, not me, and not anybody else here. As I said, I don't really consider that my patch is a rewrite, especially V4, which changes nothing substantive except removing 32-bit support. I do take your point, though - Andrew's objections should have been reason enough to name my patches another way. I don't want to take credit for Andrew's work, though, since very little of substance has actually been changed. I can understand why his remarks would give the impression that this is some kind of rewrite, but they mostly applied to my removal of numeric tracking of non-NULL values. He won that argument, so that's now irrelevant. I must also admit that I am somewhat annoyed here, since Andrew has questioned essentially ever revision I've proposed to both of the sort support patches he wrote, and in a rather bellicose way. They were mostly very modest revisions. -- 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] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 12:52 PM, Robert Haas robertmh...@gmail.com wrote: As + * with IEEE-754, we use an exponent without a sign (a 7-bit exponent + * without a sign). As to the beginning of this sentence, bringing IEEE-754 into this discussion doesn't clarify anything in my mind. I don't think most people reading these comments are likely to be familiar with IEEE-754, or want to go look it up. As to the end of the sentence, writing an exponent without a sign and then describing that as a 7-bit exponent without a sign is extremely redundant. Perhaps you were trying to say that we are similar to IEEE-754 in that we use an exponent without a sign (whatever that means) but different in that we ours is 7-bits, but it's not really clear. The IEEE-754 exponent does not have a signedness bit, but can still be logically negative (while still being manipulated like an unsigned integer for addition and subtraction type purposes). That's why it uses what they call an exponent bias - excess-127, for single precision floats, and excess-1023 for double precision floats. Once you do go and Google that, you can find many illustrative diagrams and so on. Andrew's term excess-44, on the other hand, shows no relevant pages on the first page of Google results (you have to search for excess-k to get any hint of what that even is). Regardless of anything else, I really don't think that Andrew's non-explanation of the much more obscure excess-44 is okay. I offer these not in the spirit of asking you to correct these specific things but just of explaining generally the sort of thing that causes me to prefer Andrew's version. Hope that's helpful. I think that you make some valid points. Thank you. -- 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] logical column ordering
On Thu, Mar 12, 2015 at 9:57 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: However, there's a difference between making a query silently given different results, and breaking it completely forcing the user to re-study how to write it. I think the latter is better. In that light we should just drop attnum as a column name, and use something else: maybe (attidnum, attlognum, attphysnum). So all queries in the wild would be forced to be updated, but we would not silently change semantics instead. +1 for that approach. Much better to break all of the third-party code out there definitively than to bet on which attribute people are going to want to use most commonly. I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. The fact that the new CommitFest application encourages people to blindly move things to the next CF instead of forcing patch authors to reopen the record when they update the patch is, IMHO, not good. It's just going to lead to the CF application filling up with things that the authors aren't really working on. We've got enough work to do with the patches that are actually under active development. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 12:04 PM, Robert Haas robertmh...@gmail.com wrote: In my opinion, Andrew's version is far clearer. Peter's version is full of jargon that I can't understand. I could probably figure it out with a few hours and a search engine, but that really shouldn't be necessary. Really? Andrew's version doesn't even explain what excess-K is. Surely that's obscure jargon that requires an explanation. -- 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] Exposing PG_VERSION_NUM in pg_config
Michael == Michael Paquier michael.paqu...@gmail.com writes: Michael Hi all, Michael When working on extensions or packaging, one can do some Michael grep-ing on pg_config.h to get PG_VERSION_NUM to do Michael version-related operations. An example of that is the Makefile Michael of plv8 using --include-dir with perl and a regex: MAJORVERSION is defined in Makefile.global as included by PGXS, fwiw. -- Andrew (irc:RhodiumToad) -- Sent 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: pgbench - merging transaction logs
On March 23, 2015 8:00:04 PM GMT+01:00, Fabien COELHO coe...@cri.ensmp.fr wrote: Guys, I don't see this theoretical discussion going anywhere. I think it's time to simply implement this and evaluate it on a bigger machine. Sure. I was kind of hoping that someone else would implement it, because I'm a reviewer on this one, and I do not have the bigger machine at hand either:-) If nothing happens and when I have some time, I'll do it. It wasn't specifically aimed at you... It can't take very long to implement tosimply just write to one file instead of the multiple files as now. Sure. The posix guaranteed fprintf locking should already take care of the rest. I think I would try with snprintf to reduce locking. Glibc's locking is around the streambuffer IIRC. So that's not likely to do much... -- 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] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 1:01 PM, Peter Geoghegan p...@heroku.com wrote: On Sun, Mar 22, 2015 at 1:01 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: The substance of the code is unchanged from my original patch. I didn't add diagnostic output to numeric_abbrev_abort, see my separate post about the suggestion of a GUC for that. I don't think that V2 really changed the substance, which seems to be the implication of your remarks here. You disagreed with my decision on NULL values - causing me to reconsider my position (so that's now irrelevant) - and you disagreed with not including support for 32-bit platforms. Those were the only non-stylistic changes, though. I certainly didn't change any details of the algorithm that you proposed, which, FWIW, I think is rather clever. I added a few defensive assertions to the encoding/conversion routine (which I see you've removed in V3, a long with a couple of other helpful assertions), and restructured and expanded upon the comments, but that's all. You haven't really taken into my account my V2 feedback with this V3 revision. Even after you yourself specifically called out your non-explanation of excess-44 as a possible point of confusion for readers of your patch, you didn't change or expand upon your remarks on that one iota. Guys, can we please knock it off with the dueling patches? Peter, it's really not all that helpful to take somebody else's patch, rewrite it in a form that they may or may not agree with (even if it's just the comments), and post that as v2. And when the person then posts v3 that reverts most of your changes, don't go put them all back and call that v4. Instead, you should take the hint: these are not versions of the same patch - they are two different approaches to the same problem. In this type of situation, I generally post my patch with a name like topicofthepatch-rmh-v1.patch or topicofthepatch-rmh-20150323.patch, putting my initials in there to show that this is my version of the patch, not the original author's and that it may or may not be endorsed by the original author. Having 26 versions of this patch where all of the odd-numbered versions looks like Andrew's original version and all of the even-numbered versions look like Peter's v2 is not going to make anybody happy - not either of you, not me, and not anybody else here. The typical style of review here, which I endorse, is to tell the other person what you think they should change. There is a place for directly posting a new version yourself, when the amount of cleanup required is too great to be articulated in an email, and you really want to get the thing committed; or when the original author has disappeared and you want to take up the work. But you should try to do that only in cases where you are fairly sure your work will be welcome because, quite aside from whether it ruffles any feathers, it's easy to waste a lot of time rewriting something only to find out that others don't agree with your rewrites. Discussion helps to avoid that. Furthermore, when there *is* a disagreement about something, the thing to do is to ask for (or simply wait for) the opinions of others, not dig in your heals. Andrew doesn't have a *right* to have his version committed, and you don't have a *right* to change it. What we all have a right to do is discuss, and hopefully agree on, what is best. Thanks, -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 3:07 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Mar 23, 2015 at 12:04 PM, Robert Haas robertmh...@gmail.com wrote: In my opinion, Andrew's version is far clearer. Peter's version is full of jargon that I can't understand. I could probably figure it out with a few hours and a search engine, but that really shouldn't be necessary. Really? Since I'm not in the habit of posting things to the list that I don't really believe, it shouldn't so often be necessary to ask me if I really meant it. If I posted it, and it wasn't April 1st, I meant it. Andrew's version doesn't even explain what excess-K is. Surely that's obscure jargon that requires an explanation. Well, it's possible to infer it from what he wrote afterwards, and if you don't, you can still pretty much understand the main thrust of what it's doing and why. I bet that could be rewritten to avoid using the term altogether, but even if not it's pretty clear. I don't really want to get into a nitpicking session here, but if you're wondering what I don't like as well about your version, it's things like this: + (7 binary digits is + * the smallest number of digits that allows us to represent weights + * -44 to 83 inclusive). This isn't a stupid comment, but it also isn't really commenting on the right thing. I mean, -44 to 83 is a range of 128 possible integers, so obviously it's going to need 7 bits to represent it, because 2^7=128. So, whatever information this is trying to convey, it's not quite succeeding. It's also a forward reference, because you haven't yet given any clue what's interesting about why we are trying to represent a value between -44 and 83. A bit further down: As + * with IEEE-754, we use an exponent without a sign (a 7-bit exponent + * without a sign). As to the beginning of this sentence, bringing IEEE-754 into this discussion doesn't clarify anything in my mind. I don't think most people reading these comments are likely to be familiar with IEEE-754, or want to go look it up. As to the end of the sentence, writing an exponent without a sign and then describing that as a 7-bit exponent without a sign is extremely redundant. Perhaps you were trying to say that we are similar to IEEE-754 in that we use an exponent without a sign (whatever that means) but different in that we ours is 7-bits, but it's not really clear. I offer these not in the spirit of asking you to correct these specific things but just of explaining generally the sort of thing that causes me to prefer Andrew's version. Hope that's helpful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Display of multi-target-table Modify plan nodes in EXPLAIN
On Sun, Mar 22, 2015 at 11:38 PM, Heikki Linnakangas hlinn...@iki.fi wrote: If we have promised that, I think we should break the promise. No application should depend on the details of EXPLAIN output, even if it's in JSON/YAML/XML format. EXPLAIN is used by humans, and by tools like pgAdmin that display the output for humans, so let's do what makes most sense for humans. Admin tools will have to deal with new node types, and also new plan structures in every new release anyway. And if an admin tool doesn't recognize the new format, it surely falls back to displaying them in some a reasonable generic form. +1 -- 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
[HACKERS] Materialiation is slower than non-materialized
During my routine work, I observed that incase of execution of plan having inner node of NLJ as materialized node (on top of SeqScan) is slower compared to non-materialized SeqScan node. This happens only if Work_mem is not big enough to hold all tuples in memory. To make test easy and faster, I set the work_mem as 256kB. Then result is as below: =With Material off= postgres=# set enable_material to off; SET Time: 0.225 ms postgres=# select count(tbl.id1) from tbl, tbl2 where tbl.id1tbl2.id1; count -- 49995000 (1 row) Time: 26674.299 ms postgres=# explain select count(tbl.id1) from tbl, tbl2 where tbl.id1tbl2.id1; QUERY PLAN -- Aggregate (cost=2783478.33..2783478.34 rows=1 width=4) - Nested Loop (cost=0.00..2700145.00 rows= width=4) Join Filter: (tbl.id1 tbl2.id1) - Seq Scan on tbl (cost=0.00..145.00 rows=1 width=4) - Seq Scan on tbl2 (cost=0.00..145.00 rows=1 width=4) Planning time: 0.120 ms (6 rows) =With Material on= postgres=# set enable_material to on; SET Time: 0.222 ms postgres=# select count(tbl.id1) from tbl, tbl2 where tbl.id1tbl2.id1; count -- 49995000 (1 row) Time: 32839.627 ms postgres=# explain select count(tbl.id1) from tbl, tbl2 where tbl.id1tbl2.id1; QUERY PLAN Aggregate (cost=1983648.33..1983648.34 rows=1 width=4) - Nested Loop (cost=0.00..1900315.00 rows= width=4) Join Filter: (tbl.id1 tbl2.id1) - Seq Scan on tbl (cost=0.00..145.00 rows=1 width=4) - Materialize (cost=0.00..235.00 rows=1 width=4) - Seq Scan on tbl2 (cost=0.00..145.00 rows=1 width=4) Planning time: 0.140 ms (7 rows) As per my analysis, above result is aligned with our current design. Materialization Node: Cost Calculation @ Plan time: If the results spills over to disk in case of Materialization, it considers the cost for the same in total cost. Actual Execution: Result is actually fetched from disk only even on re-scan. Scan Node: Cost Calculation @ Plan time: The cost of re-scan of SeqScan node is considered to be same scan of SeqScan node, which always assumes that the records is fetched from disk and hence disk access cost is added (As we don't know really how much memory will be available to cache during execution). Actual Execution: After first scan, once the whole records is loaded to memory (provided shared_buffer is big enough), rescan of records are read from memory only and hence it is much faster. So because of this while planning cost of Materialized node is lesser than that of SeqScan node but while execution SeqScan is faster because it fetches tuples from memory on re-scan. I am not sure if we can consider this to be a problem or not but I just wanted to share as generally it is expected by user to be Materialization faster than Non-materialized. Please provide your opinion. If we can do something about this then I can take up this work. Thanks and Regards, Kumar Rajeev Rastogi -- This e-mail and its attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient(s) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it!
Re: [HACKERS] Display of multi-target-table Modify plan nodes in EXPLAIN
On 03/22/2015 03:02 AM, Tom Lane wrote: In a green field we might choose to solve this by refactoring the output so that it's logically Multi-Table Update [ Update Target: pt1 Plan: (seq scan on pt1 here) ] [ Update Target: ft1 Remote SQL: UPDATE ref1 ... Plan: (foreign scan on ft1 here) ] [ Update Target: ft2 Remote SQL: UPDATE ref2 ... Plan: (foreign scan on ft2 here) ] [ Update Target: child3 Plan: (seq scan on child3 here) ] The Remote SQL nodes should go under the Foreign Scan nodes. but I think that ship has sailed. Changing the logical structure of EXPLAIN output like this would break clients that know what's where in JSON/YAML/XML formats, which is exactly what we said we wouldn't do with those output formats. If we have promised that, I think we should break the promise. No application should depend on the details of EXPLAIN output, even if it's in JSON/YAML/XML format. EXPLAIN is used by humans, and by tools like pgAdmin that display the output for humans, so let's do what makes most sense for humans. Admin tools will have to deal with new node types, and also new plan structures in every new release anyway. And if an admin tool doesn't recognize the new format, it surely falls back to displaying them in some a reasonable generic form. - 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] logical column ordering
On 23.3.2015 19:52, Peter Geoghegan wrote: On Mon, Mar 23, 2015 at 11:50 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Not sure how that's supposed to improve the situation? Also, when you change the status to 'stalled', it only makes it more difficult to identify why it was stalled (was it waiting for author or a review?). What might be done is tracking time since last patch/review, but I really don't know how we're going to identify that considering the problems with identifying which messages are patches. Perhaps I explained myself poorly. I am proposing having a totally automated/mechanical way of highlighting no actual change in status in the CF app. So I think we are in agreement here, or close enough. I was just talking about a somewhat arbitrary point at which patches are considered to have stalled within the CF app. Oh, right. Yes, tracking time since the last status change like this might be useful, although my experience is that many patches are stuck at some status yet there was a long discussion on the list ... Not sure if that counts as 'stalled'. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] logical column ordering
On 03/23/2015 02:32 PM, Tomas Vondra wrote: Oh, right. Yes, tracking time since the last status change like this might be useful, although my experience is that many patches are stuck at some status yet there was a long discussion on the list ... Not sure if that counts as 'stalled'. Time since last email maybe. -- 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] Remove fsync ON/OFF as a visible option?
On 2015-03-22 12:54:37 -0700, Josh Berkus wrote: On 03/22/2015 06:45 AM, Andres Freund wrote: FWIW, I think that's a myth. One I heard various versions of by now. As long as the OSs page size (4kb nearly everywhere) is different from postgres' (8kb) you can have torn pages. Even if individual filesystem page writes are atomic. ZFS's block size is larger than Linux's memory page size. That is, ZFS on Linux uses a 8kB to 128kB block size depending on which blocks you're looking at and how you have it configured. Does that make a difference at all, given that Linux's memory page size is still 4kB? No, it does not make a differencen. The page cache is still 4kb. FYI, the BTRFS folks are also claiming to be torn-page-proof, so it would be really nice to settle this. Not sure how to force the issue through testing though. I don't think this applies here. 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] GSoC 2015: Extra Jsonb functionality
On 3/21/15 12:49 PM, Dmitry Dolgov wrote: Frankly, I think the whole proposal needs to be rethought with an eye towards supporting and preserving nested elements instead of trying to just flatten everything out. Can you pls show me few examples what do you mean exactly? All the comments others have made in this thread, for starters. Your proposal seems intent on completely flattening all JSON documents, or treating them as if they were flat. I see zero chance of that being accepted. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PATCH: numeric timestamp in log_line_prefix
On 3/22/15 2:59 PM, Tomas Vondra wrote: On 22.3.2015 20:25, Fabien COELHO wrote: The proposed format is much simpler to manage in a script, and if you're interested in runtime, its formatting would be less expensive than %t and %m. Maybe, but do we really need two? How about just %M? I guess Tomas put 2 formats because there was 2 time formats to begin with, but truncating/rouding if someone really wants seconds is quite easy. Yes, that's why I added two - to reflect %t and %m. I'm OK with using just one of them - I don't really care for the milliseconds at this moment, but I'd probably choose that option. I assume we're using milli instead of micro because that's what everyone else does? It seems odd since we natively support microseconds, but I guess if milliseconds is more normal for logging that's OK. FWIW, I don't see a problem with both %T and %M (whatever M ends up meaning), but I don't really care either way. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Abbreviated keys for Numeric
Peter == Peter Geoghegan p...@heroku.com writes: Peter As I said, I don't really consider that my patch is a rewrite, Peter especially V4, which changes nothing substantive except removing Peter 32-bit support. Well, that's a hell of an except. Here's my main arguments for why 32bit support should be kept: 1. It exists and works well (and yes, I have tested it). 2. This optimization is a huge win even on very small data sets. On sorts of as few as 100 items it gives detectable (on the order of +50%) improvements. On 1000 items the speedup can easily be 3 times. So it's not just people with big data who want this; even small databases will benefit. 3. Keeping the 32bit support (and desupporting DEC_DIGITS != 4) makes it unnecessary to have #ifdefs that disable the numeric abbreviation entirely. (You don't even need those for comparative performance testing; easier to do that by tweaking the catalogs.) As against that, you have the fact that it's ~70 lines of code in one self-contained function which is 32bit-specific. So what do other people think? -- Andrew (irc:RhodiumToad) -- Sent 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: numeric timestamp in log_line_prefix
On 23.3.2015 23:02, Jim Nasby wrote: On 3/22/15 2:59 PM, Tomas Vondra wrote: On 22.3.2015 20:25, Fabien COELHO wrote: I guess Tomas put 2 formats because there was 2 time formats to begin with, but truncating/rouding if someone really wants seconds is quite easy. Yes, that's why I added two - to reflect %t and %m. I'm OK with using just one of them - I don't really care for the milliseconds at this moment, but I'd probably choose that option. I assume we're using milli instead of micro because that's what everyone else does? It seems odd since we natively support microseconds, but I guess if milliseconds is more normal for logging that's OK. That's because %m is using milliseconds. I don't think microseconds are really useful here ... FWIW, I don't see a problem with both %T and %M (whatever M ends up meaning), but I don't really care either way. Same here. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] proposal: plpgsql - Assert statement
The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:not tested Note that pgcrypto is failing 3 tests, same as in master. The new status of this patch is: Ready for Committer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] logical column ordering
On 23.3.2015 18:30, Andres Freund wrote: I think it fairly can be marked as returned with feedback for now? That will eventually be the end result, yes. If it's time to do that now, or leave the patch in the CF and only bounce it at the end, I don't know. ... which means that no useful feedback was received at all in this round for this patch. (There was lots of feedback, mind you, but as far as I can see it was all on the subject of how the patch is going to be summarily rejected unless user-visible controls are offered -- and you already know my opinion on that matter.) To me the actual blocker seems to be the implementation. Which doesn't look like it's going to be ready for 9.5; there seems to be loads of work left to do. It's hard to provide non flame-bait feedback if the patch isn't ready. I'm not sure what review you'd like to see at this stage? The version I posted at the end of February is certainly incomplete (and some of the regression tests fail), but it seemed reasonably complete to get some feedback. That is not to say parts of the patch are probably wrong / need rework. I think your approach of concentrating on the technical parts is sane, and I'd continue going that way. I do work in that direction. OTOH I think it's useful to provide some sort of minimum usable API so that people can actually use it without messing with catalogs directly. It certainly won't have all the bells and whistles, though. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 2:41 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Peter As I said, I don't really consider that my patch is a rewrite, Peter especially V4, which changes nothing substantive except removing Peter 32-bit support. Well, that's a hell of an except. I guess you're right. I'm willing to go with whatever the consensus is on that question. -- 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] logical column ordering
On 23.3.2015 22:53, Jeff Janes wrote: On Mon, Mar 23, 2015 at 11:52 AM, Tomas Vondra Sorry, the 23/2 one is the one I meant. I got confused over which of the emails listed as having an attachment but no patch was the one that actually had a patch. (If the commitfest app can't correctly deal with more than one attachment, it needs to at least give an indication that this condition may exist). But I am still getting a lot of errors during make check. 60 of 153 tests failed Some of them look like maybe a change in the expected output file didn't get included in the patch, but at least one was a coredump. Yes, there were two coredumps (as noted in the message with the patch). Not sure of the other errors - it certainly is possible I forgot to include something in the patch. Thanks for noticing this, will look into that. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] proposal GSoC 2015 task: Allow access to the database via HTTP
Hi 2015-03-22 23:28 GMT+01:00 Вадим Горбачев bmsd...@gmail.com: Hi Team. I would like to solve a problem of Allow access to the database via HTTP. But before drawing up the demand in GSOC I wanted to consult here. Therefore I will be grateful to comments from attendees here! 1. I think, will better use access to DB through the stand-alone program which not necessarily has to be on the same server. At least because it will give certain freedom in cluster systems. 2. Whether it is obligatory to use a programming language C for this purpose? After all as the stand-alone program ( frontend ) it has to be not necessarily written in the same programming language as the server ( backend ). I would prefer to use the python language for writing as I consider that this language is more clear to system administrators + to bring much more simply editings in a code. What is a benefit of this implementation for Postgres community? Proposed implementation is few lines in Python - and it is not big benefit for us. More, similar project exists. It can be interesting as well integrated project to Postgres - implemented in C as background worker (if it possible) Regards Pavel 3. What you will advise what to pass a selection stage in GSOC 2015 from postgresql?) PS: my English is poor. I ask you to forgive me for it. Best Regards, Vadim Gorbachov
Re: [HACKERS] PATCH: pgbench - merging transaction logs
Hi, Guys, I don't see this theoretical discussion going anywhere. I think it's time to simply implement this and evaluate it on a bigger machine. It can't take very long to implement tosimply just write to one file instead of the multiple files as now. The posix guaranteed fprintf locking should already take care of the rest. 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] PATCH: pgbench - merging transaction logs
Hello, Yes but for a third thread (each on a physical core) it will be 1/40 + 1/40 and so on up to roughly 40/40 for 40 cores. That is why I proposed a formula which depends on the number of threads. [...] But they aren't constant only close. It may or not show up in this case but I've noticed that often the collision rate is a lot higher than the probability would suggest, I'm not sure why, If so, I would suggested that the probability is wrong and try to understand why:-) Moreover they will write to the same cache lines for every fprintf and this is very very bad even without atomic operations. We're talking of transactions that involve network messages and possibly disk IOs on the server, so some cache issues issues within pgbench would not be a priori the main performance driver. Sure but : - good measurement is hard and by adding locking in fprintf it make its timing more noisy. This really depends on the probability of the lock collisions. If it is small enough, the impact would be negligeable. - it's against 'good practices' for scalable code. Trivial code can show that elapsed time for as low as four cores writing to same cache line in a loop, without locking or synchronization, is greater than the elapsed time for running these four loops sequentially on one core. If they write to different cache lines it scales linearly. I'm not argumenting about general scalability principles, which may or may not be relevant to the case at hand. I'm discussing whether the proposed feature can be implemented much simply with mutex instead of the current proposal which is on the heavy side, thus induces more maintenance effort latter. Now I agree that if there is a mutex it must be a short as possible and not hinder performance significantly for pertinent use case. Note that overhead evaluation by Tomas is pessimistic as it only involves read-only transactions for which all transaction details are logged. Note also that if you have 1000 cores to run pgbench and that locking may be an issue, you could still use the per-thread logs. The current discussion suggests that each thread should prepare the string off-lock (say with some sprintf) and then only lock when sending the string. This looks reasonable, but still need to be validated (i.e. the lock time would indeed be very small wrt the transaction time). -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auditing extension for PostgreSQL (Take 2)
Thanks for the review, Abhijit.
On 3/23/15 1:31 AM, Abhijit Menon-Sen wrote:
At 2015-02-24 11:22:41 -0500, da...@pgmasters.net wrote:
Patch v3 is attached.
+
+/* Function execution */
+LOG_MISC = (1 5),
The comment above LOG_MISC should be changed.
Fixed.
More fundamentally, this classification makes it easy to reuse LOGSTMT_*
(and a nice job you've done of that, with just a few additional special
cases), I don't think this level is quite enough for our needs. I think
it should at least be possible to specifically log commands that affect
privileges and roles.
I agree, but this turns out to be easier said than done. In the prior
code for instance, CREATE ROLE was classified as USER, while ALTER ROLE
.. RENAME was classified as DDL. This is because any rename gets the
command tag T_RenameStmt. CreateCommandTag does return ALTER ROLE,
but now we're in the realm of string-matching again which is not my
favorite thing. Let me see if there is a clean way to get this
accomplished. I've also felt this is the one thing I'd like to see
broken out.
I'm fond of finer categorisation for DDL as well, but I could live with
all DDL being lumped together.
I'm experimenting with a few approaches to do this without reintroducing
switch statements to test every command. That will require core changes,
but I think we can find an acceptable arrangement. I'll post a proof of
concept in a few days.
I also think finer-grained categorization would be best accomplished
with some core changes. It seemed too late to get those in for 9.5 so I
decided to proceed with what I knew could be done reliably with the idea
to improve it with core changes going forward.
I look forward to your proof-of-concept.
+ * Takes an AuditEvent and, if it log_check(), writes it to the audit
log.
I don't think log_check is the most useful name, because this sentence
doesn't tell me what the function may do. Similarly, I would prefer to
have log_acl_check be renamed acl_grants_audit or similar. (These are
all static functions anyway, I don't think a log_ prefix is needed.)
log_check() has become somewhat vestigial at this point since it is only
called from one place - I've been considering removing it and merging
into log_audit_event(). For the moment I've improved the comments.
I like acl_grants_audit() and agree that it's a clearer name. I'll
incorporate that into the next version and apply the same scheme to the
other ACL functionsas well as do a general review of naming.
+/* Free the column set */
+bms_free(tmpSet);
(An aside, really: there are lots of comments like this, which I don't
think add anything to understanding the code, and should be removed.)
I generally feel like you can't have too many comments. I think even
the less interesting/helpful comments help break the code into
functional sections for readability.
+/*
+ * We don't have access to the parsetree here, so we have to
generate
+ * the node type, object type, and command tag by decoding
+ * rte-requiredPerms and rte-relkind.
+ */
+auditEvent.logStmtLevel = LOGSTMT_MOD;
(I am also trying to find a way to avoid having to do this.)
That would be excellent.
+/* Set object type based on relkind */
+switch (class-relkind)
+{
+case RELKIND_RELATION:
+utilityAuditEvent.objectType =
OBJECT_TYPE_TABLE;
+break;
This occurs elsewhere too. But I suppose new relkinds are added less
frequently than new commands.
Well, that's the hope at least. I should mention that ALL statements
will be logged no matter what additional classification happens. The
amount of information returned may not be ideal, but nothing is ever
excluded from logging (depending on the classes selected, of course).
Again on a larger level, I'm not sure how I feel about _avoiding_ the
use of event triggers for audit logging. Regardless of whether we use
the deparse code (which I personally think is a good idea; Álvaro has
been working on it, and it looks very nice) to log extra information,
using the object access hook inevitably means we have to reimplement
the identification/classification code here.
In old pgaudit, I think that extra effort is justified by the need to
be backwards compatible with pre-event trigger releases. In a 9.5-only
version, I am not at all convinced that this makes sense.
Thoughts?
I was nervous about basing pg_audit on code that I wasn't sure would be
committed (at the time). Since pg_event_trigger_get_creation_commands()
is tied up with deparse, I honestly didn't feel like the triggers were
bringing much to the table.
That being said, I agree that the deparse code is very useful and now
looks certain to be committed for 9.5.
I have prepared a patch that brings event triggers and deparse back to
pg_audit
Fwd: [HACKERS] proposal GSoC 2015 task: Allow access to the database via HTTP
What is a benefit of this implementation for Postgres community? It will be possible to clean this task from https://wiki.postgresql.org/wiki/Todo It can be interesting as well integrated project to Postgres - implemented in C as background worker (if it possible) I.e. as I understand http_api has to work in separate process at the server of the DBMS. And why not to start the free-standing application? In this case there will be great opportunities for scaling: 1. through one http_api appendix it will be possible to be connected to several DB 2. to use pgpool 3. to share burden between servers and so forth. 4. to carry out other functions of a frontend. And what pluses writing of background worker will give? If background worker is necessary, I am ready to realize it and it will be interesting to me. But whether it will be more necessary for postgresql, than the free-standing application? 2015-03-23 19:24 GMT+03:00 Pavel Stehule pavel.steh...@gmail.com: Hi 2015-03-22 23:28 GMT+01:00 Вадим Горбачев bmsd...@gmail.com: Hi Team. I would like to solve a problem of Allow access to the database via HTTP. But before drawing up the demand in GSOC I wanted to consult here. Therefore I will be grateful to comments from attendees here! 1. I think, will better use access to DB through the stand-alone program which not necessarily has to be on the same server. At least because it will give certain freedom in cluster systems. 2. Whether it is obligatory to use a programming language C for this purpose? After all as the stand-alone program ( frontend ) it has to be not necessarily written in the same programming language as the server ( backend ). I would prefer to use the python language for writing as I consider that this language is more clear to system administrators + to bring much more simply editings in a code. What is a benefit of this implementation for Postgres community? Proposed implementation is few lines in Python - and it is not big benefit for us. More, similar project exists. It can be interesting as well integrated project to Postgres - implemented in C as background worker (if it possible) Regards Pavel 3. What you will advise what to pass a selection stage in GSOC 2015 from postgresql?) PS: my English is poor. I ask you to forgive me for it. Best Regards, Vadim Gorbachov
Re: [HACKERS] logical column ordering
On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Mar 12, 2015 at 9:57 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: However, there's a difference between making a query silently given different results, and breaking it completely forcing the user to re-study how to write it. I think the latter is better. In that light we should just drop attnum as a column name, and use something else: maybe (attidnum, attlognum, attphysnum). So all queries in the wild would be forced to be updated, but we would not silently change semantics instead. +1 for that approach. Much better to break all of the third-party code out there definitively than to bet on which attribute people are going to want to use most commonly. I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. There was a patch here, which in the commit fest is hidden behind other non-attachments in the same email: Attachment (randomize.sql http://www.postgresql.org/message-id/attachment/37076/randomize.sql) at 2015-02-27 19:10:21 http://www.postgresql.org/message-id/54f0c11d.7000...@2ndquadrant.com/ from Tomas Vondra tomas.vondra at 2ndquadrant.com But that patch failed the majority of make check checks in my hands. So I also don't know what the status is. Cheers, Jeff
Re: [HACKERS] proposal GSoC 2015 task: Allow access to the database via HTTP
as prompted to me, already there are solutions of this task: pgrest and OpenResty Then it isn't clear why this task is in TODO the sheet https://wiki.postgresql.org/wiki/Todo... Prompt what exactly is understood as Allow access to the database via HTTP? From what party to approach this task? Whether it still also is actual? Best Regards, Vadim Gorbachov 2015-03-23 1:28 GMT+03:00 Вадим Горбачев bmsd...@gmail.com: Hi Team. I would like to solve a problem of Allow access to the database via HTTP. But before drawing up the demand in GSOC I wanted to consult here. Therefore I will be grateful to comments from attendees here! 1. I think, will better use access to DB through the stand-alone program which not necessarily has to be on the same server. At least because it will give certain freedom in cluster systems. 2. Whether it is obligatory to use a programming language C for this purpose? After all as the stand-alone program ( frontend ) it has to be not necessarily written in the same programming language as the server ( backend ). I would prefer to use the python language for writing as I consider that this language is more clear to system administrators + to bring much more simply editings in a code. 3. What you will advise what to pass a selection stage in GSOC 2015 from postgresql?) PS: my English is poor. I ask you to forgive me for it. Best Regards, Vadim Gorbachov
Re: [HACKERS] logical column ordering
On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com wrote: I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. The fact that the new CommitFest application encourages people to blindly move things to the next CF instead of forcing patch authors to reopen the record when they update the patch is, IMHO, not good. It's just going to lead to the CF application filling up with things that the authors aren't really working on. We've got enough work to do with the patches that are actually under active development. Maybe there should be a stalled patch status summary, that highlights patches that have not had their status change in (say) 2 weeks. Although it wouldn't really be a status summary, since that they're mutually exclusive with each other in the CF app (e.g. a patch cannot be both Waiting on Author and Ready for Committer). -- 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] Abbreviated keys for Numeric
On Sun, Mar 22, 2015 at 1:01 PM, Andrew Gierth
and...@tao11.riddles.org.uk wrote:
The substance of the code is unchanged from my original patch. I didn't
add diagnostic output to numeric_abbrev_abort, see my separate post
about the suggestion of a GUC for that.
I don't think that V2 really changed the substance, which seems to be
the implication of your remarks here. You disagreed with my decision
on NULL values - causing me to reconsider my position (so that's now
irrelevant) - and you disagreed with not including support for 32-bit
platforms. Those were the only non-stylistic changes, though. I
certainly didn't change any details of the algorithm that you
proposed, which, FWIW, I think is rather clever. I added a few
defensive assertions to the encoding/conversion routine (which I see
you've removed in V3, a long with a couple of other helpful
assertions), and restructured and expanded upon the comments, but
that's all.
You haven't really taken into my account my V2 feedback with this V3
revision. Even after you yourself specifically called out your
non-explanation of excess-44 as a possible point of confusion for
readers of your patch, you didn't change or expand upon your remarks
on that one iota.
I see that code like this (from your V1) appears in your V3, as if V2
never happened:
+/*
+ * non-fmgr interface to the comparison routine to allow sortsupport to elide
+ * the fmgr call. The saving here is small given how slow numeric
+ * comparisons are, but there's no reason not to implement it.
+ */
This comment is totally redundant at best, and misleading at worst. Of
course we have a non-fmgr interface - it's needed to make abbreviation
work. And of course *any* opclass that performs abbreviation won't get
any great saving from cases where only the fmgr interface is elided
(e.g. numeric is not the leading sorttuple attribute). Text is unusual
in that there is a small additional saving over fmgr-elision for
obscure reasons.
Ditto for comments like this, which re-appear in V3:
+/*
+ * Ordinary (non-sortsupport) comparisons follow.
+ */
Your V3 has obsolete comments here:
+ nss = palloc(sizeof(NumericSortSupport));
+
+ /*
+ * palloc a buffer for handling unaligned packed values in addition to
+ * the support struct
+ */
+ nss-buf = palloc(VARATT_SHORT_MAX + VARHDRSZ + 1);
I still don't think you should be referencing the text opclass behavior here:
+ * number. We make no attempt to estimate the cardinality of the real values,
+ * since it plays no part in the cost model here (if the abbreviation is equal,
+ * the cost of comparing equal and unequal underlying values is comparable).
+ * We discontinue even checking for abort (saving us the hashing overhead) if
+ * the estimated cardinality gets to 100k; that would be enough to support many
+ * billions of rows while doing no worse than breaking even.
This is dubious:
+#if DEC_DIGITS != 4
+#error Numeric bases other than 1 are no longer supported
+#endif
Because there is a bunch of code within numeric.c that deals with the
DEC_DIGITS != 4 case. For example, this code has been within numeric.c
forever:
#if DEC_DIGITS == 4 || DEC_DIGITS == 2
static NumericDigit const_ten_data[1] = {10};
static NumericVar const_ten =
{1, 0, NUMERIC_POS, 0, NULL, const_ten_data};
#elif DEC_DIGITS == 1
static NumericDigit const_ten_data[1] = {1};
static NumericVar const_ten =
{1, 1, NUMERIC_POS, 0, NULL, const_ten_data};
#endif
As has this:
while (ndigits-- 0)
{
#if DEC_DIGITS == 4
*digits++ = ((decdigits[i] * 10 + decdigits[i + 1]) * 10 +
decdigits[i + 2]) * 10 + decdigits[i + 3];
#elif DEC_DIGITS == 2
*digits++ = decdigits[i] * 10 + decdigits[i + 1];
#elif DEC_DIGITS == 1
*digits++ = decdigits[i];
#else
#error unsupported NBASE
#endif
i += DEC_DIGITS;
}
I tend to think that when Tom wrote this code back in 2003, he thought
it might be useful to change DEC_DIGITS on certain builds. And so, we
ought to continue to support it to the extent that we already do,
allowing these cases to opt out of abbreviation in an informed manner
(since it seems hard to make abbreviation work with DEC_DIGITS != 4
builds). In any case, you should have deleted all this code (which
there is rather a lot of) in proposing to not support DEC_DIGITS != 4
builds generally.
Attached revision, V4, incorporates some of your V3 changes. As I
mentioned, I changed my mind on the counting of non-NULL values, which
V4 reflects...but there are also comments that now make it clear why
that might be useful.
I've retained your new allocate once approach to buffer sizing, which
seems sound if a little obscure.
This abort function code (from your V1 + V3) seems misleading:
+ if (memtupcount 1 || nss-input_count 1 || !nss-estimating)
+ return false;
It's impossible for memtupcount 1 while nss-input_count
1 as well, since the latter counts a subset of what the former
counts. So I only check the latter now.
I have not added back 32-bit support,
Re: [HACKERS] logical column ordering
Andres Freund wrote: On 2015-03-23 13:01:48 -0400, Robert Haas wrote: I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. I think it fairly can be marked as returned with feedback for now? ... which means that no useful feedback was received at all in this round for this patch. (There was lots of feedback, mind you, but as far as I can see it was all on the subject of how the patch is going to be summarily rejected unless user-visible controls are offered -- and you already know my opinion on that matter.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0
On Thu, Mar 19, 2015 at 1:02 PM, Robert Haas robertmh...@gmail.com wrote: I think this is pretty lousy. The reasons why the user wants things that way is because they created a UNIQUE index and it got bloated somehow with lots of dead tuples. So they made a new UNIQUE index on the same column and then they're planning to do a DROP INDEX CONCURRENTLY on the old one, which is maybe even now in progress. And now they start getting duplicate key failures, the avoidance of which was their whole reason for using UPSERT in the first place. If I were that user, I'd report that as a bug, and if someone told me that it was intended behavior, I'd say oh, so you deliberately designed this feature to not work some of the time?. ISTM that we need to (1) decide which operator we're using to compare and then (2) tolerate conflicts in every index that uses that operator. In most cases there will only be one, but if there are more, so be it. On reflection, I see your point. I'll try and do something about this too. -- 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] logical column ordering
Hi, On 23.3.2015 18:01, Robert Haas wrote: On Thu, Mar 12, 2015 at 9:57 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: However, there's a difference between making a query silently given different results, and breaking it completely forcing the user to re-study how to write it. I think the latter is better. In that light we should just drop attnum as a column name, and use something else: maybe (attidnum, attlognum, attphysnum). So all queries in the wild would be forced to be updated, but we would not silently change semantics instead. +1 for that approach. Much better to break all of the third-party code out there definitively than to bet on which attribute people are going to want to use most commonly. I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. The fact that the new CommitFest application encourages people to blindly move things to the next CF instead of forcing patch authors to reopen the record when they update the patch is, IMHO, not good. It's just going to lead to the CF application filling up with things that the authors aren't really working on. We've got enough work to do with the patches that are actually under active development. The last version of the patch was submitted on 24/2 by me. Not sure why it's not listed in the CF app, but it's here: http://www.postgresql.org/message-id/54ebb312.7090...@2ndquadrant.com I'm working on a new version of the patch, based on the ideas that were mentioned in this thread. I plan to post a new version within a few days, hopefully. Anyway, it's obvious this patch won't make it into 9.5 - it's a lot of subtle changes on many places, so it's not suitable for the last commitfest. But the feedback is welcome, of course. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] logical column ordering
Hi, On 23.3.2015 18:07, Peter Geoghegan wrote: On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com wrote: I'm a little confused as to the status of this patch. It's marked as Waiting on Author in the CommitFest application, and the last patch version was posted in December. The fact that the new CommitFest application encourages people to blindly move things to the next CF instead of forcing patch authors to reopen the record when they update the patch is, IMHO, not good. It's just going to lead to the CF application filling up with things that the authors aren't really working on. We've got enough work to do with the patches that are actually under active development. Maybe there should be a stalled patch status summary, that highlights patches that have not had their status change in (say) 2 weeks. Although it wouldn't really be a status summary, since that they're mutually exclusive with each other in the CF app (e.g. a patch cannot be both Waiting on Author and Ready for Committer). Not sure how that's supposed to improve the situation? Also, when you change the status to 'stalled', it only makes it more difficult to identify why it was stalled (was it waiting for author or a review?). What might be done is tracking time since last patch/review, but I really don't know how we're going to identify that considering the problems with identifying which messages are patches. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] PageRepairFragmentation performance
On Tue, Feb 3, 2015 at 4:11 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 01/31/2015 01:49 AM, Peter Geoghegan wrote: The refactoring patch certainly looks very reasonable. Ok, committed the refactoring part for now. Thanks for the review. Where are we on the rest of this, Heikki? -- 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] Auditing extension for PostgreSQL (Take 2)
Sawada Masahiko wrote: I tied to look into latest patch, but got following error. masahiko [pg_audit] $ LANG=C make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o pg_audit.o pg_audit.c pg_audit.c: In function 'log_audit_event': pg_audit.c:456: warning: ISO C90 forbids mixed declarations and code pg_audit.c: In function 'pg_audit_ddl_command_end': pg_audit.c:1436: error: 'pg_event_trigger_expand_command' undeclared (first use in this function) You need to apply my deparsing patch first, last version of which I posted here: https://www.postgresql.org/message-id/20150316234406.gh3...@alvh.no-ip.org -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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: pgbench - merging transaction logs
Guys, I don't see this theoretical discussion going anywhere. I think it's time to simply implement this and evaluate it on a bigger machine. Sure. I was kind of hoping that someone else would implement it, because I'm a reviewer on this one, and I do not have the bigger machine at hand either:-) If nothing happens and when I have some time, I'll do it. It can't take very long to implement tosimply just write to one file instead of the multiple files as now. Sure. The posix guaranteed fprintf locking should already take care of the rest. I think I would try with snprintf to reduce locking. Note that I'm first trying to evaluate logging overhead, but I'm quite far from Tomas figures at the moment, looks more like 20% overhead (12 threads read-only load, runs at about 13 tps with full logging, 16 without, repeated 5 times with the similar results). In such a case, a mutex would be a bad idea, but I must add that detailed logging is a plain bad idea as well! -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Auditing extension for PostgreSQL (Take 2)
On Tue, Mar 24, 2015 at 1:40 AM, David Steele da...@pgmasters.net wrote:
Thanks for the review, Abhijit.
On 3/23/15 1:31 AM, Abhijit Menon-Sen wrote:
At 2015-02-24 11:22:41 -0500, da...@pgmasters.net wrote:
Patch v3 is attached.
+
+/* Function execution */
+LOG_MISC = (1 5),
The comment above LOG_MISC should be changed.
Fixed.
More fundamentally, this classification makes it easy to reuse LOGSTMT_*
(and a nice job you've done of that, with just a few additional special
cases), I don't think this level is quite enough for our needs. I think
it should at least be possible to specifically log commands that affect
privileges and roles.
I agree, but this turns out to be easier said than done. In the prior
code for instance, CREATE ROLE was classified as USER, while ALTER ROLE
.. RENAME was classified as DDL. This is because any rename gets the
command tag T_RenameStmt. CreateCommandTag does return ALTER ROLE,
but now we're in the realm of string-matching again which is not my
favorite thing. Let me see if there is a clean way to get this
accomplished. I've also felt this is the one thing I'd like to see
broken out.
I'm fond of finer categorisation for DDL as well, but I could live with
all DDL being lumped together.
I'm experimenting with a few approaches to do this without reintroducing
switch statements to test every command. That will require core changes,
but I think we can find an acceptable arrangement. I'll post a proof of
concept in a few days.
I also think finer-grained categorization would be best accomplished
with some core changes. It seemed too late to get those in for 9.5 so I
decided to proceed with what I knew could be done reliably with the idea
to improve it with core changes going forward.
I look forward to your proof-of-concept.
+ * Takes an AuditEvent and, if it log_check(), writes it to the audit
log.
I don't think log_check is the most useful name, because this sentence
doesn't tell me what the function may do. Similarly, I would prefer to
have log_acl_check be renamed acl_grants_audit or similar. (These are
all static functions anyway, I don't think a log_ prefix is needed.)
log_check() has become somewhat vestigial at this point since it is only
called from one place - I've been considering removing it and merging
into log_audit_event(). For the moment I've improved the comments.
I like acl_grants_audit() and agree that it's a clearer name. I'll
incorporate that into the next version and apply the same scheme to the
other ACL functionsas well as do a general review of naming.
+/* Free the column set */
+bms_free(tmpSet);
(An aside, really: there are lots of comments like this, which I don't
think add anything to understanding the code, and should be removed.)
I generally feel like you can't have too many comments. I think even
the less interesting/helpful comments help break the code into
functional sections for readability.
+/*
+ * We don't have access to the parsetree here, so we have to
generate
+ * the node type, object type, and command tag by decoding
+ * rte-requiredPerms and rte-relkind.
+ */
+auditEvent.logStmtLevel = LOGSTMT_MOD;
(I am also trying to find a way to avoid having to do this.)
That would be excellent.
+/* Set object type based on relkind */
+switch (class-relkind)
+{
+case RELKIND_RELATION:
+utilityAuditEvent.objectType =
OBJECT_TYPE_TABLE;
+break;
This occurs elsewhere too. But I suppose new relkinds are added less
frequently than new commands.
Well, that's the hope at least. I should mention that ALL statements
will be logged no matter what additional classification happens. The
amount of information returned may not be ideal, but nothing is ever
excluded from logging (depending on the classes selected, of course).
Again on a larger level, I'm not sure how I feel about _avoiding_ the
use of event triggers for audit logging. Regardless of whether we use
the deparse code (which I personally think is a good idea; Álvaro has
been working on it, and it looks very nice) to log extra information,
using the object access hook inevitably means we have to reimplement
the identification/classification code here.
In old pgaudit, I think that extra effort is justified by the need to
be backwards compatible with pre-event trigger releases. In a 9.5-only
version, I am not at all convinced that this makes sense.
Thoughts?
I was nervous about basing pg_audit on code that I wasn't sure would be
committed (at the time). Since pg_event_trigger_get_creation_commands()
is tied up with deparse, I honestly didn't feel like the triggers were
bringing much to the table.
That being said, I agree that the deparse code is very useful and now
looks certain to be
Re: [HACKERS] proposal GSoC 2015 task: Allow access to the database via HTTP
thanks for information! I understood your idea. I originally assumed such structure: https://drive.google.com/file/d/0B4zY7CurvRqwWE9EZUpBU1FpSTQ/view?usp=sharing https://drive.google.com/file/d/0B4zY7CurvRqwSWpCRjlNVWdlMWc/view?usp=sharing https://drive.google.com/file/d/0B4zY7CurvRqwa0RYQ2tldWY5dWM/view?usp=sharing https://drive.google.com/file/d/0B4zY7CurvRqwLXZ6UzV4Q3lfclk/view?usp=sharing But your option will be more convenient: https://drive.google.com/file/d/0B4zY7CurvRqwZUJRdlBadEFIelE/view?usp=sharing I will think over it... If someone else shares councils, I will be very grateful! Best Regards, Vadim Gorbachov 2015-03-23 20:26 GMT+03:00 Pavel Stehule pavel.steh...@gmail.com: 2015-03-23 18:10 GMT+01:00 Вадим Горбачев bmsd...@gmail.com: What is a benefit of this implementation for Postgres community? It will be possible to clean this task from https://wiki.postgresql.org/wiki/Todo It can be interesting as well integrated project to Postgres - implemented in C as background worker (if it possible) I.e. as I understand http_api has to work in separate process at the server of the DBMS. And why not to start the free-standing application? In this case there will be great opportunities for scaling: 1. through one http_api appendix it will be possible to be connected to several DB 2. to use pgpool 3. to share burden between servers and so forth. 4. to carry out other functions of a frontend. And what pluses writing of background worker will give? integration to Postgres - simply (usual) deployment. Fast access to PostgreSQL shared memory, fast access to database. Now anybody can using pgpool, pgbouncer, light httpd, simple fast CGI script without problems. If background worker is necessary, I am ready to realize it and it will be interesting to me. But whether it will be more necessary for postgresql, than the free-standing application? 2015-03-23 19:24 GMT+03:00 Pavel Stehule pavel.steh...@gmail.com: Hi 2015-03-22 23:28 GMT+01:00 Вадим Горбачев bmsd...@gmail.com: Hi Team. I would like to solve a problem of Allow access to the database via HTTP. But before drawing up the demand in GSOC I wanted to consult here. Therefore I will be grateful to comments from attendees here! 1. I think, will better use access to DB through the stand-alone program which not necessarily has to be on the same server. At least because it will give certain freedom in cluster systems. 2. Whether it is obligatory to use a programming language C for this purpose? After all as the stand-alone program ( frontend ) it has to be not necessarily written in the same programming language as the server ( backend ). I would prefer to use the python language for writing as I consider that this language is more clear to system administrators + to bring much more simply editings in a code. What is a benefit of this implementation for Postgres community? Proposed implementation is few lines in Python - and it is not big benefit for us. More, similar project exists. It can be interesting as well integrated project to Postgres - implemented in C as background worker (if it possible) Regards Pavel 3. What you will advise what to pass a selection stage in GSOC 2015 from postgresql?) PS: my English is poor. I ask you to forgive me for it. Best Regards, Vadim Gorbachov
Re: [HACKERS] pg_rewind in contrib
On 03/14/2015 02:31 PM, Amit Kapila wrote:
Getting below linking error with Asserts enabled in Windows build.
1xlogreader.obj : error LNK2019: unresolved external symbol
ExceptionalCondition referenced in function
XLogReadRecord
1.\Debug\pg_rewind\pg_rewind.exe : fatal error LNK1120: 1 unresolved
externals
Am I doing anything wrong while building?
Works for me. Perhaps there were some changes to #includes that
inadvertently fixed it..
2.
msvc\clean.bat has below way to clean xlogreader.c for pg_xlogdump,
shouldn't something similar required for pg_rewind?
REM clean up files copied into contrib\pg_xlogdump
if exist contrib\pg_xlogdump\xlogreader.c del /q contrib
\pg_xlogdump\xlogreader.c
for %%f in (contrib\pg_xlogdump\*desc.c) do if not %%f==contrib\pg_xlogdump
\rmgrdesc.c del /q %%f
y.
I changed the way pg_xlogdump does that, and pg_rewind follows the new
example. (see http://www.postgresql.org/message-id/550b14a5.7060...@iki.fi)
4.
Copyright notice contains variation in terms of years
+ * Copyright (c) 2010-2015, PostgreSQL Global Development Group
+ * Copyright (c) 2013-2015, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1996-2015, PostgreSQL Global Development Group
Is there any particular reason for the same?
I've created many of the files by copying an old file and modifying
heavily. The copyright notices have been carried over from the original
files. Many of the files would still contain some of the original copied
code, while others might not. I'm not sure what the best way to deal
with that is - stamp everything as 2015, 2013-2015, or leave them as
they are. It doesn't really matter in practice.
5.
+ * relation files. Other forks are alwayes copied in toto, because we
cannot
+ * reliably track changes to
the, because WAL only contains block references
+ * for the main fork.
+ */
+static bool
+isRelDataFile(const
char *path)
Sentence near track changes to the, .. looks incomplete.
Fixed, it was supposed to be them, not the.
6.
+libpqConnect(const char *connstr)
{
..
+ /*
+ * Also check that full_page-writes are enabled. We can get torn pages if
+ * a page is
modified while we read it with pg_read_binary_file(), and we
+ * rely on full page images to fix them.
+
*/
+ str = run_simple_query(SHOW full_page_writes);
+ if (strcmp(str, on) != 0)
+
pg_fatal(full_page_writes must be enabled in the source server\n);
+ pg_free(str);
..
}
Do you think it is worth to mention this information in docs?
Added.
7.
Function execute_pagemap() exists in both copy_fetch.c and
libpq_fetch.c, are you expecting that they will get diverged
in future?
They look pretty much identical, but the copy_file_range functions they
call are in fact separate functions, and differ a lot. I have renamed
the libpq version to avoid confusion.
I have committed this, with some more kibitzing. hope I have not missed
any comments given so far. Many thanks for the review, and please
continue reviewing and testing it :-).
- 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] Auditing extension for PostgreSQL (Take 2)
On 3/23/15 1:39 PM, Sawada Masahiko wrote: On Tue, Mar 24, 2015 at 1:40 AM, David Steele da...@pgmasters.net wrote: I have prepared a patch that brings event triggers and deparse back to pg_audit based on the Alvaro's dev/deparse branch at git://git.postgresql.org/git/2ndquadrant_bdr.git (commit 0447fc5). I've updated the unit tests accordingly. I've been hesitant to post this patch as it will not work in master (though it will compile), but I don't want to hold on to it any longer since the end of the CF is nominally just weeks away. If you want to run the patch in master, you'll need to disable the pg_audit_ddl_command_end trigger. Hi, I tied to look into latest patch, but got following error. masahiko [pg_audit] $ LANG=C make gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -g -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -c -o pg_audit.o pg_audit.c pg_audit.c: In function 'log_audit_event': pg_audit.c:456: warning: ISO C90 forbids mixed declarations and code pg_audit.c: In function 'pg_audit_ddl_command_end': pg_audit.c:1436: error: 'pg_event_trigger_expand_command' undeclared (first use in this function) pg_audit.c:1436: error: (Each undeclared identifier is reported only once pg_audit.c:1436: error: for each function it appears in.) make: *** [pg_audit.o] Error 1 Am I missing something? It's my mistake. I indicated that this would compile under master - but that turns out not to be true because of this function. It will only compile cleanly in Alvaro's branch mentioned above. My apologies - this is why I have been hesitant to post this patch before. You are welcome to try with Alvaro's deparse branch or wait until it has been committed to master. I've attached patch v5 only to cleanup the warnings you saw. -- - David Steele da...@pgmasters.net diff --git a/contrib/Makefile b/contrib/Makefile index 195d447..d8e75f4 100644 --- a/contrib/Makefile +++ b/contrib/Makefile @@ -29,6 +29,7 @@ SUBDIRS = \ pageinspect \ passwordcheck \ pg_archivecleanup \ + pg_audit\ pg_buffercache \ pg_freespacemap \ pg_prewarm \ diff --git a/contrib/pg_audit/Makefile b/contrib/pg_audit/Makefile new file mode 100644 index 000..32bc6d9 --- /dev/null +++ b/contrib/pg_audit/Makefile @@ -0,0 +1,20 @@ +# pg_audit/Makefile + +MODULE = pg_audit +MODULE_big = pg_audit +OBJS = pg_audit.o + +EXTENSION = pg_audit + +DATA = pg_audit--1.0.0.sql + +ifdef USE_PGXS +PG_CONFIG = pg_config +PGXS := $(shell $(PG_CONFIG) --pgxs) +include $(PGXS) +else +subdir = contrib/pg_audit +top_builddir = ../.. +include $(top_builddir)/src/Makefile.global +include $(top_srcdir)/contrib/contrib-global.mk +endif diff --git a/contrib/pg_audit/pg_audit--1.0.0.sql b/contrib/pg_audit/pg_audit--1.0.0.sql new file mode 100644 index 000..9d9ee83 --- /dev/null +++ b/contrib/pg_audit/pg_audit--1.0.0.sql @@ -0,0 +1,22 @@ +/* pg_audit/pg_audit--1.0.0.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use CREATE EXTENSION pg_audit to load this file.\quit + +CREATE FUNCTION pg_audit_ddl_command_end() + RETURNS event_trigger + LANGUAGE C + AS 'MODULE_PATHNAME', 'pg_audit_ddl_command_end'; + +CREATE EVENT TRIGGER pg_audit_ddl_command_end + ON ddl_command_end + EXECUTE PROCEDURE pg_audit_ddl_command_end(); + +CREATE FUNCTION pg_audit_sql_drop() + RETURNS event_trigger + LANGUAGE C + AS 'MODULE_PATHNAME', 'pg_audit_sql_drop'; + +CREATE EVENT TRIGGER pg_audit_sql_drop + ON sql_drop + EXECUTE PROCEDURE pg_audit_sql_drop(); diff --git a/contrib/pg_audit/pg_audit.c b/contrib/pg_audit/pg_audit.c new file mode 100644 index 000..65c8ed2 --- /dev/null +++ b/contrib/pg_audit/pg_audit.c @@ -0,0 +1,1712 @@ +/*-- + * pg_audit.c + * + * An auditing extension for PostgreSQL. Improves on standard statement logging + * by adding more logging classes, object level logging, and providing + * fully-qualified object names for all DML and many DDL statements (See + * pg_audit.sgml for details). + * + * Copyright (c) 2014-2015, PostgreSQL Global Development Group + * + * IDENTIFICATION + * contrib/pg_audit/pg_audit.c + *-- + */ +#include postgres.h + +#include access/htup_details.h +#include access/sysattr.h +#include access/xact.h +#include catalog/catalog.h +#include catalog/objectaccess.h +#include catalog/pg_class.h +#include catalog/namespace.h +#include commands/dbcommands.h +#include catalog/pg_proc.h +#include commands/event_trigger.h +#include executor/executor.h +#include executor/spi.h +#include miscadmin.h +#include
Re: [HACKERS] logical column ordering
On Mon, Mar 23, 2015 at 11:50 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: Not sure how that's supposed to improve the situation? Also, when you change the status to 'stalled', it only makes it more difficult to identify why it was stalled (was it waiting for author or a review?). What might be done is tracking time since last patch/review, but I really don't know how we're going to identify that considering the problems with identifying which messages are patches. Perhaps I explained myself poorly. I am proposing having a totally automated/mechanical way of highlighting no actual change in status in the CF app. So I think we are in agreement here, or close enough. I was just talking about a somewhat arbitrary point at which patches are considered to have stalled within the CF app. -- 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] logical column ordering
On 23.3.2015 18:08, Jeff Janes wrote: On Mon, Mar 23, 2015 at 10:01 AM, Robert Haas robertmh...@gmail.com mailto:robertmh...@gmail.com wrote: There was a patch here, which in the commit fest is hidden behind other non-attachments in the same email: Attachment (randomize.sql http://www.postgresql.org/message-id/attachment/37076/randomize.sql) at 2015-02-27 19:10:21 http://www.postgresql.org/message-id/54f0c11d.7000...@2ndquadrant.com/ from Tomas Vondra tomas.vondra at 2ndquadrant.com http://2ndquadrant.com But that patch failed the majority of make check checks in my hands. So I also don't know what the status is. Ummm, that's not a patch but a testing script ... There was a patch submitted on 23/2, and I believe that passes most make check tests, except for two IIRC. But it's not perfect - it was the first version that mostly worked, and was somehow suitable for getting feedback. -- Tomas Vondrahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, 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] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 4:08 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: The earlier comment should make it clear that all the DEC_DIGITS != 4 support is historical. I didn't consider it appropriate to actually rip out all the #ifs; I simply tried to make it clear where the landmines were if anyone wanted to try experimenting with other DEC_DIGITS values. There is exactly one landmine: this patch. I really don't understand why you're so resistant to linking the DEC_DIGITS != 4 to it failing. After all, your V1 linked it to abbreviation silently not being enabled when numeric.c was built. -- 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] printing table in asciidoc with psql
On Sun, Mar 22, 2015 at 08:06:17PM +0900, Michael Paquier wrote:
I have updated the attached patch to do as you suggested. Please also
test the \x output. Thanks.
Indeed. If I use a specific column name like this one, I am seeing
problems with the expanded mode:
=# create table 5 2.2+^.^ (5 2.2+^.^ int);
CREATE TABLE
=# \x
Expanded display is on.
=# INSERT INTO 5 2.2+^.^ VALUES (1);
INSERT 0 1
=# table 5 2.2+^.^;
[cols=h,l,frame=none]
|
2+^|Record 1
|5 2.2+^.^ |1
|
In this case the record is printed like that:
5 2.2+.
While it should show up like that:
5 2.2+^.^
OK, fixed. It turns out you need to specify the style on each output
row ('l'/literal) so that a later data value of ^.^ is not intepreted as
a horizontal/vertial alignment specification. (Wow, it sounds like I
know what I am talking about. ;-) )
The new output is:
test= \pset format asciidoc
Output format is asciidoc.
test= \x
Expanded display is on.
test= table 5 2.2+^.^;
[cols=h,l,frame=none]
|
2+^|Record 1
-- l|5 2.2+^.^ |1
|
Notice the added 'l' next to the ''. Updated patch attached. Any
other issues?
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
new file mode 100644
index a637001..82a91ec
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*** lo_import 152801
*** 2092,2099
literalaligned/literal, literalwrapped/literal,
literalhtml/literal,
literallatex/literal (uses literaltabular/literal),
! literallatex-longtable/literal, or
! literaltroff-ms/literal.
Unique abbreviations are allowed. (That would mean one letter
is enough.)
/para
--- 2092,2099
literalaligned/literal, literalwrapped/literal,
literalhtml/literal,
literallatex/literal (uses literaltabular/literal),
! literallatex-longtable/literal,
! literaltroff-ms/literal, or literalasciidoc/literal.
Unique abbreviations are allowed. (That would mean one letter
is enough.)
/para
*** lo_import 152801
*** 2120,2126
para
The literalhtml/, literallatex/,
! literallatex-longtable/literal, and literaltroff-ms/
formats put out tables that are intended to
be included in documents using the respective mark-up
language. They are not complete documents! This might not be
--- 2120,2127
para
The literalhtml/, literallatex/,
! literallatex-longtable/literal, literaltroff-ms/,
! and literalasciidoc/
formats put out tables that are intended to
be included in documents using the respective mark-up
language. They are not complete documents! This might not be
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 7c9f28d..a96f0ef
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** _align2string(enum printFormat in)
*** 2257,2262
--- 2257,2265
case PRINT_TROFF_MS:
return troff-ms;
break;
+ case PRINT_ASCIIDOC:
+ return asciidoc;
+ break;
}
return unknown;
}
*** do_pset(const char *param, const char *v
*** 2330,2338
popt-topt.format = PRINT_LATEX_LONGTABLE;
else if (pg_strncasecmp(troff-ms, value, vallen) == 0)
popt-topt.format = PRINT_TROFF_MS;
else
{
! psql_error(\\pset: allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms\n);
return false;
}
--- 2333,2343
popt-topt.format = PRINT_LATEX_LONGTABLE;
else if (pg_strncasecmp(troff-ms, value, vallen) == 0)
popt-topt.format = PRINT_TROFF_MS;
+ else if (pg_strncasecmp(asciidoc, value, vallen) == 0)
+ popt-topt.format = PRINT_ASCIIDOC;
else
{
! psql_error(\\pset: allowed formats are unaligned, aligned, wrapped, html, latex, troff-ms, asciidoc\n);
return false;
}
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
new file mode 100644
index ac0dc27..93a517e
*** a/src/bin/psql/help.c
--- b/src/bin/psql/help.c
*** helpVariables(unsigned short int pager)
*** 351,357
fprintf(output, _( expanded (or x)toggle expanded output\n));
fprintf(output, _( fieldsep field separator for unaligned output (default '|')\n));
fprintf(output, _( fieldsep_zero set field separator in unaligned mode to zero\n));
! fprintf(output, _( format set output format [unaligned, aligned, wrapped, html, latex, ..]\n));
fprintf(output, _( footer enable or disable display of the
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 6:02 PM, Robert Haas robertmh...@gmail.com wrote: Well, not committing the patch at all would be even less invasive. But that's true of any patch, so I don't think being less invasive can be the prime goal. Of course it's usually better to be less invasive and get the same benefits, but when being less invasive means getting fewer benefits, the additional invasiveness has to be weighed against what you get out of it. I agree with that principle. But desupporting DEC_DIGITS != 4 as Andrew proposed gives no clue to how it can be worked around should someone want DEC_DIGITS != 4, as was once anticipated. Whereas a simple static assertion gives us that flexibility, with two lines of code, and without either removing or rendering entirely dead considerable swathes of numeric.c. You can argue that the code was dead anyway, but Tom didn't seem to feel that way when he wrote it. Why mess with that? There is no benefit to doing so. -- 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] Exposing PG_VERSION_NUM in pg_config
On Tue, Mar 24, 2015 at 4:06 AM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Michael == Michael Paquier michael.paqu...@gmail.com writes: Michael Hi all, Michael When working on extensions or packaging, one can do some Michael grep-ing on pg_config.h to get PG_VERSION_NUM to do Michael version-related operations. An example of that is the Makefile Michael of plv8 using --include-dir with perl and a regex: MAJORVERSION is defined in Makefile.global as included by PGXS, fwiw. Well, my point is to have something on which you can directly apply maths on without changing its shape ;) -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Abbreviated keys for Numeric
Peter == Peter Geoghegan p...@heroku.com writes: Peter Your V3 has obsolete comments here: Peter + nss = palloc(sizeof(NumericSortSupport)); Peter + Peter + /* Peter + * palloc a buffer for handling unaligned packed values in addition to Peter + * the support struct Peter + */ Peter + nss-buf = palloc(VARATT_SHORT_MAX + VARHDRSZ + 1); I don't see why it's obsolete; it still describes what the code is doing, even though the buffer is no longer contiguous with the support struct. The code makes it clear that the buffer is separate. Peter I still don't think you should be referencing the text opclass Peter behavior here: Consider it a fence against people trying to change the code for consistency. Peter This is dubious: Peter +#if DEC_DIGITS != 4 Peter +#error Numeric bases other than 1 are no longer supported Peter +#endif Peter Because there is a bunch of code within numeric.c that deals Peter with the DEC_DIGITS != 4 case. For example, this code has been Peter within numeric.c forever: The earlier comment should make it clear that all the DEC_DIGITS != 4 support is historical. I didn't consider it appropriate to actually rip out all the #ifs; I simply tried to make it clear where the landmines were if anyone wanted to try experimenting with other DEC_DIGITS values. Peter I tend to think that when Tom wrote this code back in 2003, he Peter thought it might be useful to change DEC_DIGITS on certain Peter builds. And so, we ought to continue to support it to the extent Peter that we already do, Right, but we really don't support it in any meaningful sense. The base-1 version was added for 7.4, which was also the first version with protocol-3 support. Since the V3 protocol has usable support for binary mode, people since then have been writing interfaces on the assumption that the binary representation for numerics is base-1. Since there's no good way to query the server for the value of DEC_DIGITS (and so clients don't try), this means that changing it breaks compatibility. -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 4:03 PM, Peter Geoghegan p...@heroku.com wrote: I must also admit that I am somewhat annoyed here, since Andrew has questioned essentially ever revision I've proposed to both of the sort support patches he wrote, and in a rather bellicose way. They were mostly very modest revisions. Yep, both of you are questioning each other's work, and both of you are expressing your opposition to the other person's ideas in ways that are fairly forceful, and I think it's fair to say that you are both annoyed. Believe me, I've been there. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] recovery_target_time ignored ?
On 3/23/15 12:42 AM, Venkata Balaji N wrote: Hi, Assuming that this might require a patch, i am posting this in pgsql-hackers. Apologies, if this is not the appropriate mailing list to start this discussion. I performed a PITR and saw the below message in the log file is a bit confusing. 2015-03-23 13:49:09.816 GMT-10 DB= PID=4707 LOG: *database system was interrupted; last known up at 2015-03-23 10:30:26 GMT-10* 2015-03-23 13:49:09.817 GMT-10 DB= PID=4707 LOG: *starting point-in-time recovery to 2015-03-23 10:00:26+10* 2015-03-23 13:49:09.827 GMT-10 DB= PID=4707 LOG: restored log file 0001000B0020 from archive 2015-03-23 13:49:09.888 GMT-10 DB= PID=4707 LOG: redo starts at B/2090 2015-03-23 13:49:09.937 GMT-10 DB= PID=4707 LOG: consistent recovery state reached at B/20B8 2015-03-23 13:49:09.947 GMT-10 DB= PID=4707 LOG: restored log file 0001000B0021 from archive 2015-03-23 13:49:09.950 GMT-10 DB= PID=4707 LOG: *recovery stopping before commit of transaction 16267, time 2015-03-23 13:22:37.53007+10* By mistake i gave recovery_target_time as 10:00 GMT which is 25/30 minutes behind the backup start/end time registered in the backup_label. The parameter recovery_target_time is ignored and recovery proceeds further applying all the available WAL Archive files finally ends up bringing up the database. I think it would make sense if the recovery does not proceed any further and error out with a message like recovery_target_time is behind the backup time.. please consider using the backup taken prior to the recovery_target_time I just tried it with 9.3.5 and I do get an error: LOG: starting point-in-time recovery to 2015-03-23 17:26:02.721307-04 LOG: restored log file 00010003 from archive LOG: redo starts at 0/3C8 LOG: recovery stopping before commit of transaction 1001, time 2015-03-23 18:26:01.012593-04 LOG: redo done at 0/3000228 FATAL: requested recovery stop point is before consistent recovery point Here's my recovery.conf file: restore_command = '/usr/bin/pg_backrest.pl --stanza=db archive-get %f %p' recovery_target_time = '2015-03-23 17:26:02.721307 EDT' recovery.conf file is as follows : restore_command='cp /data/pgdata9400backup/pgwalarch9400backup/%f %p ' recovery_target_time='2015-03-23 10:00:26 GMT-10' recovery_target_inclusive='true' You have '2015-03-23 10:00:26 GMT-10' in recovery.conf but the log says 'starting point-in-time recovery to 2015-03-23 10:00:26+10'. Note the - vs +. Could you check your log and recovery.conf and make sure the timezone offsets are actually different? -- - David Steele da...@pgmasters.net signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 5:47 PM, Robert Haas robertmh...@gmail.com wrote: I agree with you. Fewer and fewer people are running 32-bit systems these days, but there must surely be more people running 32-bit systems than there are running with DEC_DIGITS != 4. I think it's a stretch to say that DEC_DIGITS != 4 is supported in any meaningful sense, so I don't think de-supporting it is an issue. Of course Andrew's analysis is correct...very few people are building with DEC_DIGITS != 4. Maybe zero. That's beside the point, IMV, which is that it's less invasive to just keep the code the way it is. Desupporting DEC_DIGITS != 4, by making the code break in a general way, without reference to this patch, seems misguided. I would like the build to break in a way that makes the customizer of numeric.c realize that they can disable abbreviation manually too, and still build with DEC_DIGITS != 4. Otherwise, you better remove all the existing specialized DEC_DIGITS != 4 code, of which there is a fair bit. I don't think it makes sense to call that code historical. -- 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] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 8:54 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Mar 23, 2015 at 5:47 PM, Robert Haas robertmh...@gmail.com wrote: I agree with you. Fewer and fewer people are running 32-bit systems these days, but there must surely be more people running 32-bit systems than there are running with DEC_DIGITS != 4. I think it's a stretch to say that DEC_DIGITS != 4 is supported in any meaningful sense, so I don't think de-supporting it is an issue. Of course Andrew's analysis is correct...very few people are building with DEC_DIGITS != 4. Maybe zero. That's beside the point, IMV, which is that it's less invasive to just keep the code the way it is. Well, not committing the patch at all would be even less invasive. But that's true of any patch, so I don't think being less invasive can be the prime goal. Of course it's usually better to be less invasive and get the same benefits, but when being less invasive means getting fewer benefits, the additional invasiveness has to be weighed against what you get out of it. Desupporting DEC_DIGITS != 4, by making the code break in a general way, without reference to this patch, seems misguided. I would like the build to break in a way that makes the customizer of numeric.c realize that they can disable abbreviation manually too, and still build with DEC_DIGITS != 4. Otherwise, you better remove all the existing specialized DEC_DIGITS != 4 code, of which there is a fair bit. I don't think it makes sense to call that code historical. That's a false dichotomy. We have a bunch of code lying around with #ifdef NOT_USED around it, and that's not intended to imply that you can build with -DNOT_USED. I admit to having not looked at the patch yet, so I may have a clearer position on exactly what to do about this once I've done that. But, as a general principle, I don't accept that we must either keep the DEC_DIGITS != 4 case working in its entirety or remove it completely. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 5:41 PM, Andrew Gierth and...@tao11.riddles.org.uk wrote: Peter == Peter Geoghegan p...@heroku.com writes: Peter As I said, I don't really consider that my patch is a rewrite, Peter especially V4, which changes nothing substantive except removing Peter 32-bit support. Well, that's a hell of an except. Here's my main arguments for why 32bit support should be kept: 1. It exists and works well (and yes, I have tested it). 2. This optimization is a huge win even on very small data sets. On sorts of as few as 100 items it gives detectable (on the order of +50%) improvements. On 1000 items the speedup can easily be 3 times. So it's not just people with big data who want this; even small databases will benefit. 3. Keeping the 32bit support (and desupporting DEC_DIGITS != 4) makes it unnecessary to have #ifdefs that disable the numeric abbreviation entirely. (You don't even need those for comparative performance testing; easier to do that by tweaking the catalogs.) As against that, you have the fact that it's ~70 lines of code in one self-contained function which is 32bit-specific. So what do other people think? I agree with you. Fewer and fewer people are running 32-bit systems these days, but there must surely be more people running 32-bit systems than there are running with DEC_DIGITS != 4. I think it's a stretch to say that DEC_DIGITS != 4 is supported in any meaningful sense, so I don't think de-supporting it is an issue. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Exposing PG_VERSION_NUM in pg_config
Michael == Michael Paquier michael.paqu...@gmail.com writes: MAJORVERSION is defined in Makefile.global as included by PGXS, fwiw. Michael Well, my point is to have something on which you can directly Michael apply maths on without changing its shape ;) There's this trick: # if version 9.1 ... ifeq ($(filter-out 7.% 8.% 9.0, $(MAJORVERSION)),) # stuff endif # if version = 9.3 ifneq ($(filter-out 7.% 8.% 9.0 9.1 9.2, $(MAJORVERSION)),) # stuff endif -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Table-level log_autovacuum_min_duration
On Mon, Mar 23, 2015 at 11:07 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Michael Paquier wrote: So a worker does not see changes in postgresql.conf once it is run and processes a database, no? The launcher does run ProcessConfigFile() when SIGHUP shows up though. Maybe this is something that we should change. Yeah, checking for SIGHUP in the worker outer loop (ie once per table) seems like a reasonable thing. That sounds fine to me as well. A patch would not be complicated, but is this portion really 9.5 material? Also, this is a discussion wider than only log_autovacuum_min_duration, as autovacuum cost parameters are also available as reloptions. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Materialiation is slower than non-materialized
On 23 March 2015 21:39, Robert Haas On Mon, Mar 23, 2015 at 6:01 AM, Rajeev rastogi rajeev.rast...@huawei.com wrote: The cost of re-scan of SeqScan node is considered to be same scan of SeqScan node, which always assumes that the records is fetched from disk and hence disk access cost is added (As we don’t know really how much memory will be available to cache during execution). That's a general problem not limited to materialize nodes. We might choose to do a heap-sort rather than a quick-sort, but it may turn out that the tapes we create end up in the OS buffer cache instead of on physical storage; in fact, it's probably the common case. Scans are costed using seq_page_cost and random_page_cost, but most of the time the random page cost will not be the cost of a head seek, because we'll find the data in the OS page cache. Some of the time it really will be a head seek, but we have no idea whether that will happen in any given case. The autovacuum cost delays have this problem too: a miss in shared buffers may really be a hit in the OS page cache, but we don't know. Yes, I agree. This kind of disclaimer is inappropriate on a public mailing list. Don't send confidential information to public mailing lists. You probably don't have any legal right to control what happens to it after that, regardless of what you put in your email. Sorry for this. Generally we delete this legal message before sending mails to community but somehow missed to do the same this time. Thanks and Regards, Kumar Rajeev Rastogi. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Incorrect comment in tablecmds.c
On 2015/03/20 21:31, Bruce Momjian wrote: On Thu, Oct 23, 2014 at 06:29:07PM +0900, Etsuro Fujita wrote: I don't think that the lock level mentioned in the following comment in MergeAttributes() in tablecmds.c is right, since that that function has opened the relation with ShareUpdateExclusiveLock, not with AccessShareLock. Patch attached. 1749 /* 1750 * Close the parent rel, but keep our AccessShareLock on it until xact 1751 * commit. That will prevent someone else from deleting or ALTERing 1752 * the parent before the child is committed. 1753 */ 1754 heap_close(relation, NoLock); Agreed, patch applied. Thanks. Thanks for picking this up! 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
Re: [HACKERS] Typos in CREATE TABLE doc
On 2015/03/21 5:58, Bruce Momjian wrote: On Thu, Nov 13, 2014 at 08:30:49PM +0900, Etsuro Fujita wrote: (2014/11/13 20:07), Heikki Linnakangas wrote: On 11/13/2014 12:45 PM, Etsuro Fujita wrote: It seems to me there are typos in the reference page for CREATE TABLE. The structure of the sentence is a bit funky, but it seems correct to me. If you google for should any, you'll get a bunch of pages discussing similar sentences. I would add a comma there, though: Should any row of an insert or update operation produce a FALSE result, an exception is raised and ... I understand. So, Here is the comma patch. Patch applied. 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
Re: [HACKERS] printing table in asciidoc with psql
On Tue, Mar 24, 2015 at 8:44 AM, Bruce Momjian wrote:
Notice the added 'l' next to the ''. Updated patch attached. Any
other issues?
Ah, right. That's a good catch and your patch fixes the issue. Still,
there are problems with the tuple-only mode and the expanded mode. For
example using this example (wanted over-complicated):
create table 5 2.2+^.^ (5 2.2+^.^ text, 4 2.2+^.^ text);
insert into 5 2.2+^.^ values ('5 2.2+^.^', '4 2.2+^.^');
insert into 5 2.2+^.^ values ('2 2.2+^.^', '3 2.2+^.^');
\pset format asciidoc
-- This prints first tuple as a header (which transforms ^.^ to .
btw), 2nd as a normal row:
=# table 5 2.2+^.^ ;
[options=header,cols=l,l,frame=none]
|
|5 2.2+^.^ |4 2.2+^.^
|2 2.2+^.^ |3 2.2+^.^
|
Time: 0.451 ms
And with the expanded mode this has an incorrect format:
=# \x
Expanded display is on.
=# table 5 2.2+^.^ ;
[cols=h,l,frame=none]
|
2|
l|5 2.2+^.^ |5 2.2+^.^
l|4 2.2+^.^ |4 2.2+^.^
2|
l|5 2.2+^.^ |2 2.2+^.^
l|4 2.2+^.^ |3 2.2+^.^
|
Time: 0.555 ms
Regards,
--
Michael
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] inherit support for foreign tables
On 2015/03/23 2:57, Tom Lane wrote: I've committed this with some substantial rearrangements, notably: Thanks for taking the time to committing the patch! Thanks for the work, Hanada-san! And thank you everyone for the reviews and comments, especially Ashutosh, Horiguchi-san and Noah! * I fooled around with the PlanRowMark changes some more, mainly with the idea that we might soon allow FDWs to use rowmark methods other than ROW_MARK_COPY. The planner now has just one place where a rel's rowmark method is chosen, so as to centralize anything we need to do there. Will work on this issue. 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
Re: [HACKERS] Abbreviated keys for Numeric
On Mon, Mar 23, 2015 at 9:12 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Mar 23, 2015 at 6:02 PM, Robert Haas robertmh...@gmail.com wrote: Well, not committing the patch at all would be even less invasive. But that's true of any patch, so I don't think being less invasive can be the prime goal. Of course it's usually better to be less invasive and get the same benefits, but when being less invasive means getting fewer benefits, the additional invasiveness has to be weighed against what you get out of it. I agree with that principle. But desupporting DEC_DIGITS != 4 as Andrew proposed gives no clue to how it can be worked around should someone want DEC_DIGITS != 4, as was once anticipated. Whereas a simple static assertion gives us that flexibility, with two lines of code, and without either removing or rendering entirely dead considerable swathes of numeric.c. You can argue that the code was dead anyway, but Tom didn't seem to feel that way when he wrote it. Why mess with that? There is no benefit to doing so. I'll wait to comment on this until I have a few minutes to read TFP. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
