Re: How to handle things that change over time?

[Vik Fearing]

On 13/09/2019 17:19, Paul Jungwirth wrote:
> The SQL:2011 standard also has temporal primary keys, foreign keys,
> SELECTs, and UPDATE/DELETEs, and we're working on adding those too.

And here is a full implementation of all that:
https://github.com/xocolatl/periods/

It is packaged in both the deb and rpm repositories for PGDG.

Re: Possible bug: SQL function parameter in window frame definition

[Andrew Gierth]

> "Andrew" == Andrew Gierth  writes:

 Andrew> We could minimize the chance of breakage in a back-patched fix
 Andrew> by having query_tree_walker/mutator iterate the windowClause
 Andrew> list itself

Here is a draft patch along those lines; the intent of this one is that
no existing walker or mutator should need to change (the change to the
dependency code is basically cosmetic I believe, just avoids walking
some things twice).

Also added some tests.

-- 
Andrew (irc:RhodiumToad)

diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index dd0a7d8dac..03582781f6 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -2214,18 +2214,13 @@ find_expr_references_walker(Node *node,
 			   context->addrs);
 		}
 
-		/* query_tree_walker ignores ORDER BY etc, but we need those opers */
-		find_expr_references_walker((Node *) query->sortClause, context);
-		find_expr_references_walker((Node *) query->groupClause, context);
-		find_expr_references_walker((Node *) query->windowClause, context);
-		find_expr_references_walker((Node *) query->distinctClause, context);
-
 		/* Examine substructure of query */
 		context->rtables = lcons(query->rtable, context->rtables);
 		result = query_tree_walker(query,
    find_expr_references_walker,
    (void *) context,
-   QTW_IGNORE_JOINALIASES);
+   QTW_IGNORE_JOINALIASES |
+   QTW_EXAMINE_SORTGROUP);
 		context->rtables = list_delete_first(context->rtables);
 		return result;
 	}
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 18bd5ac903..d063bee271 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -2296,6 +2296,33 @@ query_tree_walker(Query *query,
 		return true;
 	if (walker(query->limitCount, context))
 		return true;
+	if ((flags & QTW_EXAMINE_SORTGROUP))
+	{
+		if (walker((Node *) query->groupClause, context))
+			return true;
+		if (walker((Node *) query->windowClause, context))
+			return true;
+		if (walker((Node *) query->sortClause, context))
+			return true;
+		if (walker((Node *) query->distinctClause, context))
+			return true;
+	}
+	else
+	{
+		/*
+		 * We need to walk the expressions in WindowClause nodes even if we're
+		 * not interested in SortGroupClause nodes.
+		 */
+		ListCell   *lc;
+		foreach(lc, query->windowClause)
+		{
+			WindowClause *wc = lfirst_node(WindowClause, lc);
+			if (walker(wc->startOffset, context))
+return true;
+			if (walker(wc->endOffset, context))
+return true;
+		}
+	}
 	if (!(flags & QTW_IGNORE_CTE_SUBQUERIES))
 	{
 		if (walker((Node *) query->cteList, context))
@@ -3153,6 +3180,38 @@ query_tree_mutator(Query *query,
 	MUTATE(query->havingQual, query->havingQual, Node *);
 	MUTATE(query->limitOffset, query->limitOffset, Node *);
 	MUTATE(query->limitCount, query->limitCount, Node *);
+
+	if ((flags & QTW_EXAMINE_SORTGROUP))
+	{
+		MUTATE(query->groupClause, query->groupClause, List *);
+		MUTATE(query->windowClause, query->windowClause, List *);
+		MUTATE(query->sortClause, query->sortClause, List *);
+		MUTATE(query->distinctClause, query->distinctClause, List *);
+	}
+	else
+	{
+		/*
+		 * We need to mutate the expressions in WindowClause nodes even if
+		 * we're not interested in SortGroupClause nodes.
+		 */
+		List	   *resultlist;
+		ListCell   *temp;
+
+		resultlist = NIL;
+		foreach(temp, query->windowClause)
+		{
+			WindowClause *wc = lfirst_node(WindowClause, temp);
+			WindowClause *newnode;
+
+			FLATCOPY(newnode, wc, WindowClause);
+			MUTATE(newnode->startOffset, wc->startOffset, Node *);
+			MUTATE(newnode->endOffset, wc->endOffset, Node *);
+
+			resultlist = lappend(resultlist, (Node *) newnode);
+		}
+		query->windowClause = resultlist;
+	}
+
 	if (!(flags & QTW_IGNORE_CTE_SUBQUERIES))
 		MUTATE(query->cteList, query->cteList, List *);
 	else		/* else copy CTE list as-is */
diff --git a/src/include/nodes/nodeFuncs.h b/src/include/nodes/nodeFuncs.h
index 0cb931c82c..4b5408fa9b 100644
--- a/src/include/nodes/nodeFuncs.h
+++ b/src/include/nodes/nodeFuncs.h
@@ -27,6 +27,7 @@
 #define QTW_EXAMINE_RTES_AFTER		0x20	/* examine RTE nodes after their
 			 * contents */
 #define QTW_DONT_COPY_QUERY			0x40	/* do not copy top Query */
+#define QTW_EXAMINE_SORTGROUP		0x80	/* include SortGroupNode lists */
 
 /* callback function for check_functions_in_node */
 typedef bool (*check_function_callback) (Oid func_id, void *context);
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index edc93d5729..d5fd4045f9 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -3821,3 +3821,45 @@ SELECT i, b, bool_and(b) OVER w, bool_or(b) OVER w
  5 | t | t| t
 (5 rows)
 
+-- Tests for problems with failure to walk or mutate expressions
+-- within window frame clauses.
+-- test walker (fails with collation error if expressions are not walked)
+SELECT array_ag

Re: Possible bug: SQL function parameter in window frame definition

[Tom Lane]

Andrew Gierth  writes:
> "Andrew" == Andrew Gierth  writes:
>  Andrew> We could minimize the chance of breakage in a back-patched fix
>  Andrew> by having query_tree_walker/mutator iterate the windowClause
>  Andrew> list itself

> Here is a draft patch along those lines; the intent of this one is that
> no existing walker or mutator should need to change (the change to the
> dependency code is basically cosmetic I believe, just avoids walking
> some things twice).

Hmm.  I think this is a reasonable direction to go in, but
what about groupingSets and rowMarks?

Also, in HEAD I'd be inclined to add assertions about utilityStmt
being NULL.

regards, tom lane

Thoughts on a cosntraint ?

[stan]

I have a table that consists of 3 columns.

vendor_key
mfg_key
preferred (boolean)

The idea is that a given vendor is the vendor we want to use for each
manufacturer for a given project.

I need to constrain such that  only on row of mfg, vendor and project can
be set to TRUE.

I would be interested in seeing other peoples approaches on this, also.

Any thoughts? 
-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin

Re: Thoughts on a cosntraint ?

[John W Higgins]

On Sun, Sep 29, 2019 at 12:40 PM stan  wrote:

> I have a table that consists of 3 columns.
>
> vendor_key
> mfg_key
> preferred (boolean)
>
> The idea is that a given vendor is the vendor we want to use for each
> manufacturer for a given project.
>
> I need to constrain such that  only on row of mfg, vendor and project can
> be set to TRUE.
>
>
Partial unique index

Example 3 on this page
https://www.postgresql.org/docs/current/indexes-partial.html is exactly
what you are looking for.

John W Higgins

>
>

Re: Thoughts on a cosntraint ?

[Rob Sargent]

> On Sep 29, 2019, at 1:52 PM, John W Higgins  wrote:
> 
> On Sun, Sep 29, 2019 at 12:40 PM stan  > wrote:
> I have a table that consists of 3 columns.
> 
> vendor_key
> mfg_key
> preferred (boolean)
> 
> The idea is that a given vendor is the vendor we want to use for each
> manufacturer for a given project.
> 
> I need to constrain such that  only on row of mfg, vendor and project can
> be set to TRUE.
> 
> 
> Partial unique index
> 
> Example 3 on this page 
> https://www.postgresql.org/docs/current/indexes-partial.html 
>  is exactly 
> what you are looking for.
> 
> John W Higgins
> 
Yes but I have to wonder about one-and-only-one preferred vendor: if there are 
many known, acceptable vendors, wouldn’t a ranking be more useful?  On a 
crucial commodity it is common practice to continuously use more than one 
vendor (i.e. multiple preferred vendors).

Re: Thoughts on a cosntraint ?

[Adrian Klaver]

On 9/29/19 12:40 PM, stan wrote:

I have a table that consists of 3 columns.

vendor_key
mfg_key
preferred (boolean)

The idea is that a given vendor is the vendor we want to use for each
manufacturer for a given project.

I need to constrain such that  only on row of mfg, vendor and project can
be set to TRUE.


Where is the project id?



I would be interested in seeing other peoples approaches on this, also.

Any thoughts?




--
Adrian Klaver
adrian.kla...@aklaver.com

Redis 16 times faster than Postgres?

[Colin 't Hart]

Hi,

Can someone take a look at this blog post?
https://www.peterbe.com/plog/redis-vs-postgres-blob-of-json

Can Redis really be 16 times faster than Postgres? Surely Postgres can get
closer to the raw speed of the hardware than 1 order of magnitude?

Thanks,

Colin

Re: Redis 16 times faster than Postgres?

[Nathan Woodrow]

Redis is a in memory database so I would except it to be always much
faster..

On Mon., 30 Sep. 2019, 7:42 am Colin 't Hart,  wrote:

> Hi,
>
> Can someone take a look at this blog post?
> https://www.peterbe.com/plog/redis-vs-postgres-blob-of-json
>
> Can Redis really be 16 times faster than Postgres? Surely Postgres can get
> closer to the raw speed of the hardware than 1 order of magnitude?
>
> Thanks,
>
> Colin
>

Re: Redis 16 times faster than Postgres?

[Ron]

On 9/29/19 4:42 PM, Colin 't Hart wrote:

Hi,

Can someone take a look at this blog post?
https://www.peterbe.com/plog/redis-vs-postgres-blob-of-json

Can Redis really be 16 times faster than Postgres? Surely Postgres can get 
closer to the raw speed of the hardware than 1 order of magnitude?


Redis is an in-memory key-value database. PostgreSQL... isn't.


--
Angular momentum makes the world go 'round.

Re: Redis 16 times faster than Postgres?

[Morris de Oryx]

Sigh. I despair of "16x faster" and "20x faster" headlines that ignore the
raw numbers. *The worst numbers in there are far below the threshold of
user perception*. Unless these results are compounded by running in a loop,
they are meaningless. Not immeasurable, just meaningless.

https://www.nngroup.com/articles/response-times-3-important-limits/

That piece is from 1993, but the human nervous system hasn't changed since
then.

Re: Redis 16 times faster than Postgres?

[Ron]

On 9/29/19 7:01 PM, Morris de Oryx wrote:
Sigh. I despair of "16x faster" and "20x faster" headlines that ignore the 
raw numbers. *The worst numbers in there are far below the threshold of 
user perception*. Unless these results are compounded by running in a 
loop, they are meaningless. Not immeasurable, just meaningless.


https://www.nngroup.com/articles/response-times-3-important-limits/

That piece is from 1993, but the human nervous system hasn't changed since 
then.


Back-end web servers don't have human nervous systems. Faster response time 
means that a give bit of hardware can support a much higher load, saving you 
money


--
Angular momentum makes the world go 'round.

Re: Redis 16 times faster than Postgres?

[Morris de Oryx]

> Back-end web servers don't have human nervous systems. Faster response
time means that a give bit of hardware can support
>  a much higher load, saving you money

Fair point, I can't argue against it as stated. Although I have no way of
know if this person's site is *ever* overloaded or has any chance of saving
money. Other sites do for sure. Not knocking using in-memory caches, they
can be a life-saver.

Like everyone else here, I've been to a *lot* of meetings down the year
that came down to arguing about optimizations that could not pay for
themselves before the heat death of the universe. As such, I've developed
an allergy to context-free performance comparisons.

On Mon, Sep 30, 2019 at 10:25 AM Ron  wrote:

> On 9/29/19 7:01 PM, Morris de Oryx wrote:
>
> Sigh. I despair of "16x faster" and "20x faster" headlines that ignore the
> raw numbers. *The worst numbers in there are far below the threshold of
> user perception*. Unless these results are compounded by running in a
> loop, they are meaningless. Not immeasurable, just meaningless.
>
> https://www.nngroup.com/articles/response-times-3-important-limits/
>
> That piece is from 1993, but the human nervous system hasn't changed since
> then.
>
>
> Back-end web servers don't have human nervous systems. Faster response
> time means that a give bit of hardware can support a much higher load,
> saving you money
>
> --
> Angular momentum makes the world go 'round.
>

Re: Redis 16 times faster than Postgres?

[Steve Litt]

On Mon, 30 Sep 2019 07:46:14 +1000
Nathan Woodrow  wrote:

> Redis is a in memory database so I would except it to be always much
> faster..

Is there a way to have Redis periodically update an on-disk backup?
That would be great, but otherwise you're at the mercy of your power
company (here in Central Florida it's routine for power to go down and
stay down for five hours).

SteveT
 
Steve Litt
Author: The Key to Everyday Excellence
http://www.troubleshooters.com/key
Twitter: http://www.twitter.com/stevelitt

Re: Redis 16 times faster than Postgres?

[raf]

Steve Litt wrote:

> On Mon, 30 Sep 2019 07:46:14 +1000
> Nathan Woodrow  wrote:
> 
> > Redis is a in memory database so I would except it to be always much
> > faster..
> 
> Is there a way to have Redis periodically update an on-disk backup?
> That would be great, but otherwise you're at the mercy of your power
> company (here in Central Florida it's routine for power to go down and
> stay down for five hours).
> 
> SteveT
>  
> Steve Litt
> Author: The Key to Everyday Excellence
> http://www.troubleshooters.com/key
> Twitter: http://www.twitter.com/stevelitt

i don't know but voltdb, another in-memory database,
replicates to other instances which can be in different
geographical locations and so not prone to a single
power failure. perhaps all in-memory databases are
aware of the need for this.

cheers,
raf

Re: Redis 16 times faster than Postgres?

[Ron]

On 9/29/19 8:09 PM, Steve Litt wrote:

On Mon, 30 Sep 2019 07:46:14 +1000
Nathan Woodrow  wrote:


Redis is a in memory database so I would except it to be always much
faster..

Is there a way to have Redis periodically update an on-disk backup?
That would be great, but otherwise you're at the mercy of your power
company (here in Central Florida it's routine for power to go down and
stay down for five hours).


It would be criminal for it not to have an async writer process flushing 
modified pages to disk.  And to not have a UPS that you've tested.


--
Angular momentum makes the world go 'round.

Re: Redis 16 times faster than Postgres?

[Michael Paquier]

On Sun, Sep 29, 2019 at 04:52:15PM -0500, Ron wrote:
> On 9/29/19 4:42 PM, Colin 't Hart wrote:
> Redis is an in-memory key-value database. PostgreSQL... isn't.

Well, I think that you have never heard about the urban legend of
running Postgres on scissors then and this reminds me of this blog
post:
http://www.databasesoup.com/2015/02/running-with-scissors-mode.html

Note that sometimes I have run Postgres on a tmpfs as well to test
some specific patches.  So that can be done, and of course that's
unsafe.
--
Michael


signature.asc
Description: PGP signature

Re: Redis 16 times faster than Postgres?

[Ben Chobot]

On Sep 29, 2019, at 8:44 PM, Ron  wrote:
> 
> On 9/29/19 8:09 PM, Steve Litt wrote:
>> On Mon, 30 Sep 2019 07:46:14 +1000
>> Nathan Woodrow  wrote:
>> 
>>> Redis is a in memory database so I would except it to be always much
>>> faster..
>> Is there a way to have Redis periodically update an on-disk backup?
>> That would be great, but otherwise you're at the mercy of your power
>> company (here in Central Florida it's routine for power to go down and
>> stay down for five hours).
> 
> It would be criminal for it not to have an async writer process flushing 
> modified pages to disk.  And to not have a UPS that you've tested.

It's perfectly reasonable to use Redis as a caching layer without any 
persistence at all. In such cases, flushing state to disk is a waste of 
resources. 

(For other use cases, yes, Redis allows you to flush state to disk.)

Re: Possible bug: SQL function parameter in window frame definition

[Andrew Gierth]

> "Tom" == Tom Lane  writes:

 >> Here is a draft patch along those lines; the intent of this one is
 >> that no existing walker or mutator should need to change (the change
 >> to the dependency code is basically cosmetic I believe, just avoids
 >> walking some things twice).

 Tom> Hmm.  I think this is a reasonable direction to go in, but
 Tom> what about groupingSets and rowMarks?

groupingSets ultimately contains nothing but numbers which are
meaningless without reference to the matching groupClause list. So
anything that cares about those is really going to have to process them
in its Query case in the walker function in order to get at both
clauses.

Similarly, rowMarks contains indexes into the rangetable (and no
recursive substructure at all), so it's likewise better processed at the
Query level.

 Tom> Also, in HEAD I'd be inclined to add assertions about utilityStmt
 Tom> being NULL.

Yup.

-- 
Andrew (irc:RhodiumToad)