Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 27 September 2014 23:23, Peter Geoghegan p...@heroku.com wrote: On Thu, Sep 25, 2014 at 1:48 PM, Simon Riggs si...@2ndquadrant.com wrote: I hate the fact that you have written no user facing documentation for this feature. Attached patch adds a commit to the existing patchset. For the convenience of reviewers, I've uploaded and made publicly accessible a html build of the documentation. This page is of most interest: http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html My request was for the following... Agree command semantics by producing these things * Explanatory documentation (Ch6.4 Data Manipulation - Upsert) * SQL Reference Documentation (INSERT) * Test cases for feature * Test cases for concurrency * Test cases for pgbench because it forces you to show in detail how the command works. Adding a few paragraphs to the INSERT page with two quick examples is not the same level of detail at all and leaves me with the strong impression my input has been assessed as ON CONFLICT IGNORE. Examples of the following are needed ON CONFLICT UPDATE optionally accepts a WHERE clause condition. When provided, the statement only procedes with updating if the condition is satisfied. Otherwise, unlike a conventional UPDATE, the row is still locked for update. Note that the condition is evaluated last, after a conflict has been identified as a candidate to update. Question arising: do you need to specify location criteria, or is this an additional filter? When/why would we want that? Failure to anticipate and prevent would-be unique violations originating in some other unique index than the single unique index that was anticipated as the sole source of would-be uniqueness violations can result in updating a row other than an existing row with conflicting values (if any). In English, please How would you do if colA = 3 then ignore else update? No explanation of why the CONFLICTING() syntax differs from OLD./NEW. syntax used in triggers The page makes no mention of the upsert problem, nor is any previous code mentioned. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On Sat, Sep 27, 2014 at 11:21 PM, Simon Riggs si...@2ndquadrant.com wrote:
My request was for the following...
Agree command semantics by producing these things
* Explanatory documentation (Ch6.4 Data Manipulation - Upsert)
Do you really think I could get an entire chapter out of this?
* SQL Reference Documentation (INSERT)
* Test cases for feature
* Test cases for concurrency
All of these were added. There are two new sets of isolation tests,
one per variant of the new clause (IGNORE/UPDATE).
* Test cases for pgbench
They're not part of the patch proper, but as I've already mentioned I
have pgbench based stress-tests on Github. There is a variety of
test-cases that test the feature under high concurrency:
https://github.com/petergeoghegan/upsert
Examples of the following are needed
ON CONFLICT UPDATE optionally accepts a WHERE clause condition.
Yes, I realized I missed an example of that one the second I hit
send. The MVCC interactions of this are discussed within
transaction-iso.html, FWIW.
Question arising: do you need to specify location criteria, or is this
an additional filter? When/why would we want that?
It is an additional way to specify a predicate/condition to UPDATE on.
There might be a kind of redundancy, if you decided to repeat the
constrained values in the predicate too, but if you're using the WHERE
clause sensibly there shouldn't be. So your UPDATE's full predicate
is sort of the union of the constrained values that the conflict path
was taken for, plus whatever you put in the WHERE clause, but not
quite because they're evaluated at different times (as explained
within transaction-iso.html).
How would you do if colA = 3 then ignore else update?
Technically, you can't do that exact thing. IGNORE is just for quickly
dealing with ETL-type problems (and it is reasonable to use it without
one particular unique index in mind, unlike ON CONFLICT UPDATE) -
think pgloader. But if you did this:
INSERT INTO tab(colB) values('foo') ON CONFLICT UPDATE set colB =
CONFLICTING(colB) WHERE colA != 3
Then you would achieve almost the same thing. You wouldn't have
inserted or updated anything if the only rows considered had a colA of
3, but any such rows considered would be locked, which isn't the same
as IGNOREing them.
No explanation of why the CONFLICTING() syntax differs from OLD./NEW.
syntax used in triggers
Why should it be the same?
The page makes no mention of the upsert problem, nor is any previous
code mentioned.
What's the upsert problem? I mean, apart from the fact that we don't
have it. Note that it is documented that one of the two outcomes is
guaranteed.
I should have updated the plpgsql looping subxact example, though.
--
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] Proper query implementation for Postgresql driver
Hi. I'm a core developer on npgsql (the Postgresql .NET provider), we're hard at work on a major 3.0 version. I have a fundamental question that's been bugging me for a while and would like to get your opinion on it. Npgsql currently supports three basic query modes: simple, client-side parameters and prepared. The first two use the Postgresql simple query flow (client-side parameters means the user specifies parameters programmatically, just like with prepared queries, but the actual substitution work is done client-side). Prepared uses the Postgresql extended query flow. According to the Postgresql docs (49.2.2), the simple query flow, the format of the retrieved values is always text. This creates a burden where npgsql needs to parse textual (and locale-specific!) info (e.g. dates, money). The situation is even worse when doing client-side parameters, since npgsql has to *create* textual representations that match what Postgresql is expecting. The workaround for this issue up to now has been to switch to culture-invariant formatting (e.g. lc_monetary=C), but this approach imposes the setting on users and affects functions in ways they don't necessarily want. I would, in theory, love to switch the entire thing to binary and thereby avoid all textual parsing once and for all. If I understand correctly, this means all queries must be implemented as extended queries, with numerous extra client-server roundtrips - which are a bit hard to stomach. Section 49.1.2 of the manual also states that the unnamed prepared statement and portal are optimized for the case of executing a query only once, hinting that this is the proper way to do things - but this optimization still cannot not eliminate the extra roundtrips mentioned above (PREPARE, BIND, EXECUTE). Can someone please let me know what the recommended/best practice here would be? Thanks, Shay
Re: [HACKERS] Proper query implementation for Postgresql driver
On 9/28/14, 11:53 AM, Shay Rojansky wrote: I would, in theory, love to switch the entire thing to binary and thereby avoid all textual parsing once and for all. If I understand correctly, this means all queries must be implemented as extended queries, with numerous extra client-server roundtrips - which are a bit hard to stomach. Section 49.1.2 of the manual also states that the unnamed prepared statement and portal are optimized for the case of executing a query only once, hinting that this is the proper way to do things - but this optimization still cannot not eliminate the extra roundtrips mentioned above (PREPARE, BIND, EXECUTE). You don't have to do multiple round-trips for that; you can just send all the messages in one go. See how e.g. libpq does it in PQexecParams(). .marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 09/28/2014 09:40 AM, Peter Geoghegan wrote: No explanation of why the CONFLICTING() syntax differs from OLD./NEW. syntax used in triggers Why should it be the same? Both can be seen as cases where you refer to a field of a tuple, which is usually done with FOO.bar. -- Andreas Karlsson -- Sent 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 {UPDATE | IGNORE}
On 09/28/2014 03:40 PM, Peter Geoghegan wrote: Do you really think I could get an entire chapter out of this? Yes. It might be a short chapter, but once you extract the existing upsert example from the docs and how why the naïve approach doesn't work there'll be enough to go on. People get this wrong a *lot*. http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING http://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ http://stackoverflow.com/q/17267417/398670 http://stackoverflow.com/q/1109061/398670 I'm happy to help with documenting it. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch to support SEMI and ANTI join removal
On 2014-09-28 17:32:21 +1300, David Rowley wrote: My understanding of foreign keys is that any pending foreign key triggers will be executed just before the query completes, so we should only ever encounter pending foreign key triggers during planning when we're planning a query that's being executed from somewhere like a volatile function or trigger function, if the outer query has updated or deleted some records which are referenced by a foreign key. Note that foreign key checks also can be deferred. So the window for these cases is actually larger. So I think with the check for pending triggers at planning time this is safe at least for queries being planned right before they're executed, but you've caused me to realise that I'll probably need to do some more work on this for when it comes to PREPARE'd queries, as it looks like if we executed a prepared query from inside a volatile function or trigger function that was called from a DELETE or UPDATE statement that caused foreign key triggers to be queued, and we'd happened to have removed some INNER JOINs when we originally planned that prepare statement, then that would be wrong. I'm wondering whether this wouldn't actually be better handled by some sort of 'one time filter' capability for joins. When noticing during planning that one side of the join is nullable attach a check to the join node. Then, whenever that check returns true, skip checking one side of the join and return rows without looking at that side. That capability might also be interesting for more efficient planning of left joins that partially have a constant join expression. 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] Proper query implementation for Postgresql driver
Marko Tiikkaja ma...@joh.to writes: On 9/28/14, 11:53 AM, Shay Rojansky wrote: [ complaint about multiple round trips in extended protocol ] You don't have to do multiple round-trips for that; you can just send all the messages in one go. See how e.g. libpq does it in PQexecParams(). Right. The key thing to understand is that after an error, the server skips messages until it sees a Sync. So you can send out Parse, Bind, Execute, Sync in one packet and not have to worry that the server will attempt to execute a query that failed parsing or whatever. 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 to support SEMI and ANTI join removal
David Rowley dgrowle...@gmail.com writes: Please correct anything that sounds wrong here, but my understanding is that we'll always plan a query right before we execute it, with the exception of PREPARE statements where PostgreSQL will cache the query plan when the prepare statement is first executed. If this optimization only works in that scenario, it's dead in the water, because that assumption is unsupportable. The planner does not in general use the same query snapshot as the executor, so even in an immediate- execution workflow there could have been data changes (caused by other transactions) between planning and execution. Why do you need such an assumption? 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] Proper query implementation for Postgresql driver
Thanks guys, that makes perfect sense to me... Am Sonntag, 28. September 2014 schrieb Tom Lane : Marko Tiikkaja ma...@joh.to javascript:; writes: On 9/28/14, 11:53 AM, Shay Rojansky wrote: [ complaint about multiple round trips in extended protocol ] You don't have to do multiple round-trips for that; you can just send all the messages in one go. See how e.g. libpq does it in PQexecParams(). Right. The key thing to understand is that after an error, the server skips messages until it sees a Sync. So you can send out Parse, Bind, Execute, Sync in one packet and not have to worry that the server will attempt to execute a query that failed parsing or whatever. regards, tom lane
[HACKERS] Time measurement format - more human readable
Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way time spent executing the SQL
commands is printed out.
The idea is to have a human readable time printed, e.g.:
Time: 1:32:15.45 m:s:ms
Time: 2_10:12:55:444.033 d_h:m:s:ms
Attached you can find a patch without any regression tests for that as
this is practically impossible to test with regression tests. The
duration of an SQL command (even though using pg_sleep) would differ
on each machine and even between consecutive runs. Therefore one
cannot specify a static expected output.
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.
My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.
This modification does not require any interaction with user.
It may create backward compatibility issues if some SQL developers
assumed that the format is always milis.micros.
regards
bogdan
From 25b2e3f9d888ecf0cc6fe0fbb569004cf9ce315b Mon Sep 17 00:00:00 2001
From: Bogdan Pilch bogdan.pi...@opensynergy.com
Date: Sat, 16 Aug 2014 23:20:18 +0200
Subject: [PATCH] BPI: Implemented enhancement f timing feature (displaying
time in a more readable way).
---
src/bin/psql/common.c | 75 +--
1 file changed, 73 insertions(+), 2 deletions(-)
diff --git a/src/bin/psql/common.c b/src/bin/psql/common.c
index c08c813..9c7f1ff 100644
--- a/src/bin/psql/common.c
+++ b/src/bin/psql/common.c
@@ -25,10 +25,18 @@
#include mbprint.h
+#define TIMING_BUFFER_SIZE 64
+
+#define SECONDS_DENOMINATOR (1000.0)
+#define MINUTES_DENOMINATOR (60.0 * SECONDS_DENOMINATOR)
+#define HOURS_DENOMINATOR (60.0 * MINUTES_DENOMINATOR)
+#define DAYS_DENOMINATOR (24.0 * HOURS_DENOMINATOR)
+
static bool ExecQueryUsingCursor(const char *query, double *elapsed_msec);
static bool command_no_begin(const char *query);
static bool is_select_command(const char *query);
+void ms2str_format(double intime, char *out);
/*
* setQFout
@@ -880,6 +888,7 @@ SendQuery(const char *query)
bool OK = false;
bool on_error_rollback_savepoint = false;
static bool on_error_rollback_warning = false;
+ char timing_buf[TIMING_BUFFER_SIZE];
if (!pset.db)
{
@@ -1063,8 +1072,11 @@ SendQuery(const char *query)
PQclear(results);
/* Possible microtiming output */
- if (pset.timing)
- printf(_(Time: %.3f ms\n), elapsed_msec);
+ if (pset.timing) {
+ ms2str_format(elapsed_msec, timing_buf);
+ //printf(_(Time: %.3f ms\n), elapsed_msec);
+ printf(_(Time: %s\n), timing_buf);
+ }
/* check for events that may occur during query execution */
@@ -1748,3 +1760,62 @@ expand_tilde(char **filename)
return;
}
+
+/*
+ * Fill in the supplied buffer with nice time broken down to dd:hh:mm:ss:ms.us
+ *
+ */
+void ms2str_format(double intime, char *out)
+{
+ int days, hours, minutes, seconds;
+ double ms;
+
+ days = (int) (intime / (DAYS_DENOMINATOR));
+ intime -= ((double)days * DAYS_DENOMINATOR);
+ hours = (int) (intime / (HOURS_DENOMINATOR));
+ intime -= ((double)hours * HOURS_DENOMINATOR);
+ minutes = (int) (intime / (MINUTES_DENOMINATOR));
+ intime -= ((double)minutes * MINUTES_DENOMINATOR);
+ seconds = (int) (intime / (SECONDS_DENOMINATOR));
+ intime -= ((double)seconds * SECONDS_DENOMINATOR);
+ ms = (intime);
+
+ if (days 0)
+ sprintf(out, %d_%d:%d:%d:%.02f d_h:m:s:ms, days, hours, minutes, seconds, ms);
+ else if (hours 0)
+ sprintf(out, %d:%d:%d:%.02f h:m:s:ms, hours, minutes, seconds, ms);
+ else if (minutes 0)
+ sprintf(out, %d:%d:%.02f m:s:ms, minutes, seconds, ms);
+ else if (seconds 0)
+ sprintf(out, %d:%.02f s:ms, seconds, ms);
+ else
+ sprintf(out, %.02f ms, ms);
+}
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Tab expansion - on/off feature
Hi,
I have created a small patch to postgres source (in particular the
psql part of it) that modifies the way tab expansion is handled.
The idea is to be able to toggle tab expansion, having the default set
to ON (as it is now). If turned off, tab characters on command line in
interactive mode are not evaluated nor expanded, but just copied.
Tab expansion can either be turned off using command line option (-C) or
controlled by \tab internal command of psql.
Attached you can find a patch. I haven't created any regression tests
as tab expansion works only in interactive mode.
My patch is relative to origin/REL9_4_STABLE branch as that is the one
I started from.
My plea is to have this change merged into the main stream so that it
becomes available in upcoming releases.
This modification introduces new (optional) command line option and a
new internal backslash command.
It does not create any backward compatibility issues as the default
behavior remains unchanged.
regards
bogdan
From e3ba6cda83b64246c2b4d3df01f62444f4b37c9d Mon Sep 17 00:00:00 2001
From: Bogdan Pilch bogdan.pi...@opensynergy.com
Date: Sun, 7 Sep 2014 18:59:12 +0200
Subject: [PATCH] Implemented support for turning off/on tab completion (in
readline).
---
src/bin/psql/command.c | 20
src/bin/psql/help.c | 1 +
src/bin/psql/input.c| 9 +
src/bin/psql/input.h| 1 +
src/bin/psql/mainloop.c | 3 +++
src/bin/psql/settings.h | 1 +
src/bin/psql/startup.c | 7 ++-
src/bin/psql/tab-complete.c | 9 +
src/bin/psql/tab-complete.h | 1 +
9 files changed, 51 insertions(+), 1 deletion(-)
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 741a72d..e14b15b 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1343,6 +1343,26 @@ exec_command(const char *cmd,
free(value);
}
+ /* \tab -- toggle tab completion */
+ else if (strcmp(cmd, tab) == 0)
+ {
+ char *opt = psql_scan_slash_option(scan_state,
+ OT_NORMAL, NULL, false);
+
+ if (opt)
+ pset.tab_completion = ParseVariableBool(opt);
+ else
+ pset.tab_completion = !pset.tab_completion;
+ if (!pset.quiet)
+ {
+ if (pset.tab_completion)
+puts(_(Tab completion is on.));
+ else
+puts(_(Tab completion is off.));
+ }
+ free(opt);
+ }
+
/* \timing -- toggle timing of queries */
else if (strcmp(cmd, timing) == 0)
{
diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c
index 3aa3c16..afc90b8 100644
--- a/src/bin/psql/help.c
+++ b/src/bin/psql/help.c
@@ -87,6 +87,7 @@ usage(void)
printf(_(\nInput and output options:\n));
printf(_( -a, --echo-all echo all input from script\n));
+ printf(_( -C, --tab-completion-off turn off tab completion\n));
printf(_( -e, --echo-queries echo commands sent to server\n));
printf(_( -E, --echo-hiddendisplay queries that internal commands generate\n));
printf(_( -L, --log-file=FILENAME send session log to file\n));
diff --git a/src/bin/psql/input.c b/src/bin/psql/input.c
index aa32a3f..96f73c6 100644
--- a/src/bin/psql/input.c
+++ b/src/bin/psql/input.c
@@ -263,6 +263,15 @@ decode_history(void)
/*
+ * Just a wrapper function for readline setup
+ */
+void
+set_input_completion(void)
+{
+ set_readline_completion();
+}
+
+/*
* Put any startup stuff related to input in here. It's good to maintain
* abstraction this way.
*
diff --git a/src/bin/psql/input.h b/src/bin/psql/input.h
index 1d10a22..ad1eede 100644
--- a/src/bin/psql/input.h
+++ b/src/bin/psql/input.h
@@ -41,6 +41,7 @@
char *gets_interactive(const char *prompt);
char *gets_fromFile(FILE *source);
+void set_input_completion(void);
void initializeInput(int flags);
bool saveHistory(char *fname, int max_lines, bool appendFlag, bool encodeFlag);
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index c3aff20..92546e0 100644
--- a/src/bin/psql/mainloop.c
+++ b/src/bin/psql/mainloop.c
@@ -123,6 +123,9 @@ MainLoop(FILE *source)
fflush(stdout);
+ /* Modify readline settings if necessary */
+ set_input_completion();
+
/*
* get another line
*/
diff --git a/src/bin/psql/settings.h b/src/bin/psql/settings.h
index 0a60e68..7e5e98c 100644
--- a/src/bin/psql/settings.h
+++ b/src/bin/psql/settings.h
@@ -89,6 +89,7 @@ typedef struct _psqlSettings
uint64 lineno; /* also for error reporting */
bool timing; /* enable timing of all queries */
+ bool tab_completion; /* enable/disable tab completion in interactive mode */
FILE *logfile; /* session log file handle */
diff --git a/src/bin/psql/startup.c b/src/bin/psql/startup.c
index 45653a1..df9d720 100644
--- a/src/bin/psql/startup.c
+++ b/src/bin/psql/startup.c
@@ -121,6 +121,7 @@ main(int argc, char *argv[])
pset.copyStream = NULL;
pset.cur_cmd_source = stdin;
pset.cur_cmd_interactive = false;
+ pset.tab_completion = true;
/* We rely on unmentioned fields of pset.popt to start out 0/false/NULL
Re: [HACKERS] Tab expansion - on/off feature
Bogdan Pilch bog...@matfyz.cz writes: The idea is to be able to toggle tab expansion, having the default set to ON (as it is now). If turned off, tab characters on command line in interactive mode are not evaluated nor expanded, but just copied. You can already suppress tab expansion with the -n switch. Do we really need another way to do it? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Turning off HOT/Cleanup sometimes
On 27 September 2014 09:29, Andres Freund and...@anarazel.de wrote: On 2014-09-27 10:23:33 +0300, Heikki Linnakangas wrote: This patch has gotten a fair amount of review, and has been rewritten once during the commitfest. I think it's pretty close to being committable, the only remaining question seems to be what to do with system catalogs. I'm marking this as Returned with feedback, I take it that Simon can proceed from here, outside the commitfest. FWIW, I don't think it is, even with that. As is it seems very likely that it's going to regress a fair share of workloads. At the very least it needs a fair amount of benchmarking beforehand. There is some doubt there. We've not seen a workload that does actually exhibit a negative behaviour. I'm not saying one doesn't exist, but it does matter how common/likely it is. If anyone can present a performance test case that demonstrates a regression, I think it will make it easier to discuss how wide that case is and what we should do about it. Discussing whether to do various kinds of limited pruning are moot until that is clear. My memory was that it took months for people to understand the frequent update use case, since catching it in flagrante delicto was hard. That may be the case here, or not, but negative-benefit experimental results very welcome. Updated patch attached to address earlier comments. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services hot_disable.v6.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 28 September 2014 08:40, Peter Geoghegan p...@heroku.com wrote:
On Sat, Sep 27, 2014 at 11:21 PM, Simon Riggs si...@2ndquadrant.com wrote:
My request was for the following...
Agree command semantics by producing these things
* Explanatory documentation (Ch6.4 Data Manipulation - Upsert)
...
INSERT INTO tab(colB) values('foo') ON CONFLICT UPDATE set colB =
CONFLICTING(colB) WHERE colA != 3
Then you would achieve almost the same thing. You wouldn't have
inserted or updated anything if the only rows considered had a colA of
3, but any such rows considered would be locked, which isn't the same
as IGNOREing them.
No explanation of why the CONFLICTING() syntax differs from OLD./NEW.
syntax used in triggers
Why should it be the same?
Good question. What could be wrong with making up new syntax?
The obvious answer is because we would simply have nothing to guide
us. No principles that can be applied, just opinions.
My considered opinion is that the above syntax is
* non-standard
* inconsistent with what we have elsewhere
* an additional item for implementors to handle
I could use more emotive words here, but the above should suffice to
cover my unease at inventing new SQL constructs. This is Postgres.
What worries me the most is that ORM implementors everywhere will
simply ignore our efforts, leaving us with something we'd much rather
we didn't have. As a possible committer of this feature, I would not
wish to put my name to that. You will need one a committer who will do
that.
Which brings me back to the SQL Standard, which is MERGE. We already
know the MERGE command does not fully and usefully define its
concurrent behaviour; I raised this 6 years ago. It's not clear to me
that that we couldn't more closely define the behaviour for a subset
of the command.
If we implement MERGE, then we will help ORM developers do less work
to support Postgres, which will encourage adoption.
My proposal would be to implement only a very limited syntax for MERGE
in this release, replacing this
INSERT INTO tab(colB) values('foo') ON CONFLICT UPDATE set colB =
CONFLICTING(colB) WHERE colA != 3
with this...
MERGE INTO tab USING VALUES ('foo')
WHEN NOT MATCHED THEN
INSERT (colB)
WHEN MATCHED THEN
UPDATE SET colB = NEW.p1
and throwing ERROR: full syntax for MERGE not implemented yet if
people stretch too far.
If there is some deviation from the standard, it can be explained
clearly, though I don't see we would need to do that - we can extend
beyond the standard to explain the concurrent behaviour. And we will
be a lot closer to getting full MERGE also.
Doing MERGE syntax is probably about 2 weeks work, which is better
than 2 weeks per ORM to support the new Postgres-only syntax.
Thanks for your efforts to bring this to a conclusion.
--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On Sun, Sep 28, 2014 at 1:17 PM, Simon Riggs si...@2ndquadrant.com wrote:
MERGE INTO tab USING VALUES ('foo')
WHEN NOT MATCHED THEN
INSERT (colB)
WHEN MATCHED THEN
UPDATE SET colB = NEW.p1
and throwing ERROR: full syntax for MERGE not implemented yet if
people stretch too far.
That isn't the MERGE syntax either. Where is the join?
I've extensively discussed why I think we should avoid calling
something upsert-like MERGE, as you know:
http://www.postgresql.org/message-id/flat/CAM3SWZRP0c3g6+aJ=yydgyactzg0xa8-1_fcvo5xm7hrel3...@mail.gmail.com#CAM3SWZRP0c3g6+aJ=yydgyactzg0xa8-1_fcvo5xm7hrel3...@mail.gmail.com
We *should* have a MERGE feature, but one that serves the actual MERGE
use-case well. That is an important use-case; it just isn't the one
I'm interested in right now.
FWIW, I agree that it wouldn't be much work to do this - what you
present here really is just a different syntax for what I have here
(which isn't MERGE). I think it would be counter-productive to pursue
this, though. Also, what about limiting the unique indexes under
consideration?
There was informal meeting of this at the dev meeting a in 2012.
--
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] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On Sun, Sep 28, 2014 at 1:31 PM, Peter Geoghegan p...@heroku.com wrote: There was informal meeting of this at the dev meeting a in 2012. I mean: There was informal agreement that as long as we're working on a feature that makes useful, UPSERT-like guarantees, we shouldn't use the MERGE syntax. MERGE clearly benefits (in ways only relevant to the use-case it targets) from having the leeway to not care about what someone with the UPSERT use-case would call race conditions. -- 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] Time measurement format - more human readable
On 29/09/14 00:49, Bogdan Pilch wrote: Hi, I have created a small patch to postgres source (in particular the psql part of it) that modifies the way time spent executing the SQL commands is printed out. The idea is to have a human readable time printed, e.g.: Time: 1:32:15.45 m:s:ms Time: 2_10:12:55:444.033 d_h:m:s:ms Attached you can find a patch without any regression tests for that as this is practically impossible to test with regression tests. The duration of an SQL command (even though using pg_sleep) would differ on each machine and even between consecutive runs. Therefore one cannot specify a static expected output. My patch is relative to origin/REL9_4_STABLE branch as that is the one I started from. My plea is to have this change merged into the main stream so that it becomes available in upcoming releases. This modification does not require any interaction with user. It may create backward compatibility issues if some SQL developers assumed that the format is always milis.micros. regards bogdan If this is a forced, and not optional, then I think it is a backward step. IMnsHO For programmatic analysis: either milis.micros or the number, of seconds (with a fractional part), would be okay. I would be happy if there was a configuration parameter to control it. At least a simple boolean to choose between the new old format - but better still, would be a time format string to allow people to choose the representation they consider most appropriate for their own needs. Having a configuration parameter set to the original format, would also avoid unnecessary backwards compatibility problems! Cheers, Gavin
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 29/09/14 09:31, Peter Geoghegan wrote:
On Sun, Sep 28, 2014 at 1:17 PM, Simon Riggs si...@2ndquadrant.com wrote:
MERGE INTO tab USING VALUES ('foo')
WHEN NOT MATCHED THEN
INSERT (colB)
WHEN MATCHED THEN
UPDATE SET colB = NEW.p1
and throwing ERROR: full syntax for MERGE not implemented yet if
people stretch too far.
That isn't the MERGE syntax either. Where is the join?
I've extensively discussed why I think we should avoid calling
something upsert-like MERGE, as you know:
http://www.postgresql.org/message-id/flat/CAM3SWZRP0c3g6+aJ=yydgyactzg0xa8-1_fcvo5xm7hrel3...@mail.gmail.com#CAM3SWZRP0c3g6+aJ=yydgyactzg0xa8-1_fcvo5xm7hrel3...@mail.gmail.com
We *should* have a MERGE feature, but one that serves the actual MERGE
use-case well. That is an important use-case; it just isn't the one
I'm interested in right now.
FWIW, I agree that it wouldn't be much work to do this - what you
present here really is just a different syntax for what I have here
(which isn't MERGE). I think it would be counter-productive to pursue
this, though. Also, what about limiting the unique indexes under
consideration?
There was informal meeting of this at the dev meeting a in 2012.
How about have a stub page for MERGE, saying it is not implemented yet,
but how about considering UPSERT - or something of that nature?
I can suspect that people are much more likely to look for 'MERGE' in an
index, or look for 'MERGE' in the list of SQL commands, than 'UPSERT'.
Cheers,
Gavin
--
Sent 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 {UPDATE | IGNORE}
On Sun, Sep 28, 2014 at 3:41 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: How about have a stub page for MERGE, saying it is not implemented yet, but how about considering UPSERT - or something of that nature? I can suspect that people are much more likely to look for 'MERGE' in an index, or look for 'MERGE' in the list of SQL commands, than 'UPSERT'. Seems reasonable. What I have a problem with is using the MERGE syntax to match people's preexisting confused ideas about what MERGE does. If we do that, it'll definitely bite us when we go to make what we'd be calling MERGE do what MERGE is actually supposed to do. I favor clearly explaining that. -- 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] Time measurement format - more human readable
On 9/28/14, 7:49 AM, Bogdan Pilch wrote: I have created a small patch to postgres source (in particular the psql part of it) that modifies the way time spent executing the SQL commands is printed out. The idea is to have a human readable time printed There are already a wide range of human readable time interval output formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE If none of those are acceptable to you, it would be difficult but not impossible to justify something new. I could see tweaking one of those into a slightly updated new style aimed at this specific job, especially since it doesn't have to consider things like negative intervals. There's value in printing time measurements using one of these interval styles sometimes, instead of the relatively raw values given right now. It would need to be an option though, and one that let the user allow choosing any of the supported interval formats. I personally would prefer to never see the existing format the number is reported in go away--too much software already expects it to be there, in that format. But adding this human readable version after that, when the user asks specifically for it, could be an acceptable addition. So there's a rough spec for the job you'd have to take on here. I'd expect it to expand in scope almost immediately to also consider the output of similar time intervals from mechanisms like log_min_duration_statement, too though, rather than just focusing on psql timing data. There's a whole second round of almost inevitable scope creep to working on this. If you were hoping what you submitted might be considered directly, sorry; that's not going to happen. Handling input and output of times and dates is a very deep topic, and small patches trying to adjust such behavior without grappling with the full complexity are normally rejected outright. There are cases where the existing code just has simple hacks in there now that could easily be whacked around. But once the topic of cleaning those up appears, swapping to an alternate simple hack is rarely how that goes. It normally heads toward considering the full right thing to do to handle all cases usefully. -- Greg Smith greg.sm...@crunchydatasolutions.com Chief PostgreSQL Evangelist - http://crunchydatasolutions.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] Missing newlines in verbose logs of pg_dump, introduced by RLS patch
On Sun, Sep 28, 2014 at 1:36 AM, Michael Paquier michael.paqu...@gmail.com wrote: Hi all, Recent commit 491c029 introducing RLS has broken a bit the verbose logs of pg_dump, one message missing a newline: + if (g_verbose) + write_msg(NULL, reading row-security enabled for table \%s\, + tbinfo-dobj.name); The patch attached corrects that. The schema name is missing... attached patch add it. 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 diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 12811a8..ab169c9 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -2803,8 +2803,8 @@ getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables) continue; if (g_verbose) - write_msg(NULL, reading row-security enabled for table \%s\, - tbinfo-dobj.name); + write_msg(NULL, reading row-security enabled for table \%s\.\%s\\n, + tbinfo-dobj.namespace-dobj.name, tbinfo-dobj.name); /* * Get row-security enabled information for the table. @@ -2833,8 +2833,8 @@ getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables) } if (g_verbose) - write_msg(NULL, reading row-security policies for table \%s\\n, - tbinfo-dobj.name); + write_msg(NULL, reading row-security policies for table \%s\.\%s\\n, + tbinfo-dobj.namespace-dobj.name, tbinfo-dobj.name); /* * select table schema to ensure regproc name is qualified if needed -- Sent 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 {UPDATE | IGNORE}
On 29/09/14 11:57, Peter Geoghegan wrote: On Sun, Sep 28, 2014 at 3:41 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: How about have a stub page for MERGE, saying it is not implemented yet, but how about considering UPSERT - or something of that nature? I can suspect that people are much more likely to look for 'MERGE' in an index, or look for 'MERGE' in the list of SQL commands, than 'UPSERT'. Seems reasonable. What I have a problem with is using the MERGE syntax to match people's preexisting confused ideas about what MERGE does. If we do that, it'll definitely bite us when we go to make what we'd be calling MERGE do what MERGE is actually supposed to do. I favor clearly explaining that. Opinionated I may be, but I wanted stay well clear of the syntax minefield in this area - as I still have at least a vestigial instinct for self preservation! :-) -- Sent 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 {UPDATE | IGNORE}
On Sun, Sep 28, 2014 at 6:15 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: What I have a problem with is using the MERGE syntax to match people's preexisting confused ideas about what MERGE does. If we do that, it'll definitely bite us when we go to make what we'd be calling MERGE do what MERGE is actually supposed to do. I favor clearly explaining that. Opinionated I may be, but I wanted stay well clear of the syntax minefield in this area - as I still have at least a vestigial instinct for self preservation! :-) To be clear: I don't think Simon is confused about this at all, which is why I'm surprised that he suggested it. -- 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] Missing newlines in verbose logs of pg_dump, introduced by RLS patch
On Mon, Sep 29, 2014 at 10:07 AM, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: The schema name is missing... attached patch add it. Ah, right, thanks. It didn't occur to me immediately :) Your patch looks good to me, and you are updating as well the second message that missed the schema name in getRowSecurity. Regards, -- Michael
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 29/09/14 14:20, Peter Geoghegan wrote: On Sun, Sep 28, 2014 at 6:15 PM, Gavin Flower gavinflo...@archidevsys.co.nz wrote: What I have a problem with is using the MERGE syntax to match people's preexisting confused ideas about what MERGE does. If we do that, it'll definitely bite us when we go to make what we'd be calling MERGE do what MERGE is actually supposed to do. I favor clearly explaining that. Opinionated I may be, but I wanted stay well clear of the syntax minefield in this area - as I still have at least a vestigial instinct for self preservation! :-) To be clear: I don't think Simon is confused about this at all, which is why I'm surprised that he suggested it. More specifically, I have only lightly read this thread - and while I think the functionality is useful, I have not thought about it any real depth. I was thinking more along the lines that if I needed functionality like this, where how might I look for it. I was remembering my problems looking up syntax in COBOL after coming from FORTRAN ( other languages) - some concepts had different names and the philosophy was significantly different in places. The relevance here, is that peoples' background in other DBMS knowledge of SQL standards affect what they expect, as well as preventing unnecessary conflicts between PostgreSQL SQL standards (as far as is practicable sensible). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] KNN-GiST with recheck
On Fri, Sep 26, 2014 at 10:49:42AM +0400, Alexander Korotkov wrote:
Does this also fix the identical PostGIS problem or is there something
PostGIS needs to do?
This patch provides general infrastructure for recheck in KNN-GiST. PostGIS
need corresponding change in its GiST opclass. Since PostGIS already define
-
and # operators as distance to bounding box border and bounding box center,
it can't change their behaviour.
it has to support new operator exact distance in opclass.
Ah, OK, so they just need something that can be used for the recheck. I
think they currently use ST_Distance() for that. Does it have to be an
operator? If they defined an operator for ST_Distance(), would
ST_Distance() work too for KNN-GiST?
In summary, you still create a normal GiST index on the column:
http://shisaa.jp/postset/postgis-postgresqls-spatial-partner-part-3.html
CREATE INDEX planet_osm_line_ref_index ON planet_osm_line(ref);
which indexes by the bounding box. The new code will allow ordered
index hits to be filtered by something like ST_Distance(), rather than
having to a LIMIT 50 in a CTE, then call ST_Distance(), like this:
EXPLAIN ANALYZE WITH distance AS (
SELECT way AS road, ref AS route
FROM planet_osm_line
WHERE highway = 'secondary'
ORDER BY ST_GeomFromText('POLYGON((14239931.42
3054117.72,14239990.49 3054224.25,14240230.15 3054091.38,14240171.08
3053984.84,14239931.42 3054117.72))', 900913) # way
LIMIT 50
)
SELECT ST_Distance(ST_GeomFromText('POLYGON((14239931.42
3054117.72,14239990.49 3054224.25,14240230.15 3054091.38,14240171.08
3053984.84,14239931.42 3054117.72))', 900913), road) AS true_distance, route
FROM distance
ORDER BY true_distance
LIMIT 1;
Notice the CTE uses # (bounding box center), and then the outer query
uses ST_Distance and LIMIT 1 to find the closest item.
Excellent!
--
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] Collation-aware comparisons in GIN opclasses
On Tue, Sep 16, 2014 at 06:56:24PM +0400, Alexander Korotkov wrote: On Tue, Sep 16, 2014 at 12:14 PM, Emre Hasegeli e...@hasegeli.com wrote: Changing the default opclasses should work if we make pg_dump --binary-upgrade dump the default opclasses with indexes and exclusion constraints. I think it makes sense to do so in --binary-upgrade mode. I can try to come with a patch for this. Can you explain it a bit more detail? I didn't get it. pg_upgrade uses pg_dump --binary-upgrade to dump the schema of the old database. Now, it generates CREATE INDEX statements without explicit opclass if opclass is the default. We can change pg_dump to generate the statements with opclass even if opclass is the default in --binary-upgrade mode. Thanks, I get it. I checked pg_dump implementation. It appears to be not as easy as it could be. pg_dump doesn't form index definition by itself. It calls pg_get_indexdef function. This function have no option to dump names of default opclasses. Since we can't change behaviour of old postgres version, we have to make pg_dump form index definition by itself. Well, the server is also operating in binary-upgrade mode, so you could have the server-side function pg_get_indexdef() behave differently for pg_upgrade. -- 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] Collation-aware comparisons in GIN opclasses
On Mon, Sep 15, 2014 at 03:42:20PM -0700, Peter Geoghegan wrote: On Mon, Sep 15, 2014 at 12:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: No. And we don't know how to change the default opclass without breaking things, either. Is there a page on the Wiki along the lines of things that we would like to change if ever there is a substantial change in on-disk format that will break pg_upgrade? ISTM that we should be intelligently saving those some place, just as Redhat presumably save up ABI-breakage over many years for the next major release of RHEL. Alexander's complaint is a good example of such a change, IMV. Isn't it more or less expected that the day will come when we'll make a clean break? It is on the TODO page under pg_upgrade: Desired changes that would prevent upgrades with pg_upgrade -- 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] jsonb format is pessimal for toast compression
On 09/26/2014 06:20 PM, Josh Berkus wrote: Overall, I'm satisfied with the performance of the length-and-offset patch. Oh, also ... no bugs found. So, can we get Beta3 out now? -- 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] jsonb format is pessimal for toast compression
Josh Berkus j...@agliodbs.com writes: So, can we get Beta3 out now? If nobody else steps up and says they want to do some performance testing, I'll push the latest lengths+offsets patch tomorrow. Are any of the other open items listed at https://wiki.postgresql.org/wiki/PostgreSQL_9.4_Open_Items things that we must-fix-before-beta3? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Add generate_series(numeric, numeric)
Hi,
I am newbie in postgresql development, so i took easy item in Todo list
Add generate_series(numeric, numeric). First, i changed function with
analogue funcionality (generate_series_timestamp) and added new object in
pg_proc (object id is 6000). My changes successfully was compiled.
I have found some problems when code was tested. (remark step=1.0)
1) STATEMENT: SELECT generate_series(1.0,6.1);
1: generate_series (typeid = 1700, len = -1, typmod = -1, byval = f)
1: generate_series = 1.0 (typeid = 1700, len = -1, typmod = -1, byval =
f)
1: generate_series = 2.0 (typeid = 1700, len = -1, typmod = -1, byval =
f)
1: generate_series = 3.0 (typeid = 1700, len = -1, typmod = -1, byval =
f)
1: generate_series = 4.0 (typeid = 1700, len = -1, typmod = -1, byval =
f)
1: generate_series = 5.0 (typeid = 1700, len = -1, typmod = -1, byval =
f)
1: generate_series = 6.0 (typeid = 1700, len = -1, typmod = -1, byval =
f)
Function work.
2) STATEMENT: SELECT * FROM generate_series(1.0, 6.1)
1: generate_series (typeid = 1700, len = -1, typmod = -1, byval = f)
make_result(): NUMERIC w=0 d=0 POS 0001
CURRENT:: NUMERIC w=0 d=1 POS 0001
FINISH:: NUMERIC w=0 d=1 POS 0006 1000
STEP:: NUMERIC w=0 d=0 POS 0001
make_result(): NUMERIC w=0 d=1 POS 0002
CURRENT:: NUMERIC w=32639 d=16255 NEG (more )
And postgres was crashed.
Could you help to find mistakes?
Some questions:
1) Is correct using Numeric in generate_series_numeric_fctx instead of
NumericVar?
2) How do you determine object id for new function? Maybe you're looking
for last object id in catalog directory (src/include/catalog/pg_*.h) and
increase by one last object id.
P.S. Sorry, I have made mistakes in message, because english isn't native
language.
From 916bfe117e464fe9185f294cbf4c9979758e7651 Mon Sep 17 00:00:00 2001
From: Malyugin Platon malugi...@gmail.com
Date: Mon, 29 Sep 2014 09:40:56 +0700
Subject: [PATCH] Add function generate_series(numeric, numeric)
---
src/backend/utils/adt/numeric.c | 73 ++---
src/include/catalog/pg_proc.h | 2 ++
src/include/utils/builtins.h| 1 +
3 files changed, 72 insertions(+), 4 deletions(-)
diff --git a/src/backend/utils/adt/numeric.c b/src/backend/utils/adt/numeric.c
index 19d0bdc..73cd169 100644
--- a/src/backend/utils/adt/numeric.c
+++ b/src/backend/utils/adt/numeric.c
@@ -14,7 +14,7 @@
* Copyright (c) 1998-2014, PostgreSQL Global Development Group
*
* IDENTIFICATION
- * src/backend/utils/adt/numeric.c
+ * src/backend/utils/adt/numeric.cn
*
*-
*/
@@ -35,13 +35,13 @@
#include utils/builtins.h
#include utils/int8.h
#include utils/numeric.h
-
+#include funcapi.h
/* --
* Uncomment the following to enable compilation of dump_numeric()
* and dump_var() and to get a dump of any result produced by make_result().
- * --
+ * -- */
#define NUMERIC_DEBUG
- */
+
/* --
@@ -260,6 +260,14 @@ typedef struct NumericVar
} NumericVar;
+typedef struct
+{
+ Numeric current;
+ Numeric finish;
+ Numeric step;
+ int step_sign;
+} generate_series_numeric_fctx;
+
/* --
* Some preinitialized constants
* --
@@ -1221,6 +1229,63 @@ numeric_floor(PG_FUNCTION_ARGS)
PG_RETURN_NUMERIC(res);
}
+Datum
+generate_series_numeric(PG_FUNCTION_ARGS)
+{
+ generate_series_numeric_fctx *fctx;
+ FuncCallContext *funcctx;
+ Numeric res;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ Numeric start = PG_GETARG_NUMERIC(0);
+ Numeric finish = PG_GETARG_NUMERIC(1);
+ MemoryContext oldcontext;
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
+ fctx = (generate_series_numeric_fctx *)palloc(sizeof(generate_series_numeric_fctx));
+
+ fctx-current = start;
+ fctx-finish = finish;
+ fctx-step = make_result(const_one);
+ fctx-step_sign = 1;
+
+ funcctx-user_fctx = fctx;
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+
+ fctx = funcctx-user_fctx;
+ dump_numeric(CURRENT:, fctx-current);
+ dump_numeric(FINISH:, fctx-finish);
+ dump_numeric(STEP:, fctx-step);
+
+ res = fctx-current;
+
+ if (fctx-step_sign 0 ?
+ cmp_numerics(fctx-current, fctx-finish) = 0 :
+ cmp_numerics(fctx-current, fctx-finish) = 0)
+ {
+ NumericVar current;
+ NumericVar step;
+ NumericVar new;
+
+ init_var_from_num(fctx-current, current);
+ init_var_from_num(fctx-step, step);
+ add_var(current, step, new);
+
+ fctx-current = make_result(new);
+
+ SRF_RETURN_NEXT(funcctx, NumericGetDatum(res));
+ }
+ else
+ {
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
/*
* Implements the numeric version of the width_bucket() function
* defined by SQL2003. See also width_bucket_float8().
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 0af1248..7530b64 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 09/29/2014 06:41 AM, Gavin Flower wrote:
I can suspect that people are much more likely to look for 'MERGE' in an
index, or look for 'MERGE' in the list of SQL commands, than 'UPSERT'.
and/or to be looking for MySQL's:
ON DUPLICATE KEY {IGNORE|UPDATE}
What astonishes me when I look around at how other RDBMS users solve
this is how many of them completely ignore concurrency issues. e.g. in
this SO question:
http://stackoverflow.com/q/108403/398670
there's an alarming lack of concern for concurrency, just a couple of
links to :
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/
(BTW, that article contains some useful information about corner cases
any upsert approach should test and deal with).
Similar with Oracle: Alarming lack of concern for concurrency among users:
http://stackoverflow.com/q/237327/398670
Useful article:
http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On Sun, Sep 28, 2014 at 8:53 PM, Craig Ringer cr...@2ndquadrant.com wrote: there's an alarming lack of concern for concurrency, just a couple of links to : http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ (BTW, that article contains some useful information about corner cases any upsert approach should test and deal with). Did you find some of those links from my pgCon slides, or independently? I'm well aware of those issues, FWIW. Avoiding repeating the mistakes of others is something that I thought about from an early stage. -- 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] INSERT ... ON CONFLICT {UPDATE | IGNORE}
On 09/29/2014 12:03 PM, Peter Geoghegan wrote: On Sun, Sep 28, 2014 at 8:53 PM, Craig Ringer cr...@2ndquadrant.com wrote: there's an alarming lack of concern for concurrency, just a couple of links to : http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ (BTW, that article contains some useful information about corner cases any upsert approach should test and deal with). Did you find some of those links from my pgCon slides, or independently? I'm well aware of those issues, FWIW. Avoiding repeating the mistakes of others is something that I thought about from an early stage. Independently. I'm very glad to see you've looked over those issues. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Time measurement format - more human readable
On 2014-09-28 20:32:30 -0400, Gregory Smith wrote: On 9/28/14, 7:49 AM, Bogdan Pilch wrote: I have created a small patch to postgres source (in particular the psql part of it) that modifies the way time spent executing the SQL commands is printed out. The idea is to have a human readable time printed There are already a wide range of human readable time interval output formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE He's talking about psql's \timing... 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] Time measurement format - more human readable
Andres Freund and...@2ndquadrant.com writes: On 2014-09-28 20:32:30 -0400, Gregory Smith wrote: On 9/28/14, 7:49 AM, Bogdan Pilch wrote: I have created a small patch to postgres source (in particular the psql part of it) that modifies the way time spent executing the SQL commands is printed out. There are already a wide range of human readable time interval output formats available in the database; see the list at http://www.postgresql.org/docs/current/static/datatype-datetime.html#INTERVAL-STYLE-OUTPUT-TABLE He's talking about psql's \timing... Indeed. Still, it seems like this has more downside than upside. It seems likely to break some peoples' scripts, and where exactly is the groundswell of complaint that the existing format is unreadable? TBH, I've not heard even one complaint about that before today. On the other hand, the number of complaints we will get if we change the format is likely to be more than zero. 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
