Re: Should we document how column DEFAULT expressions work?

[Pantelis Theodosiou]

On Thu, Jun 27, 2024 at 1:11 AM Tom Lane  wrote:
>
> David Rowley  writes:
> > Maybe I'm slow on the uptake, but I've yet to see anything here where
> > time literals act in a special way DEFAULT constraints. This is why I
> > couldn't understand why we should be adding documentation about this
> > under CREATE TABLE.
>
> It's not that the parsing rules are any different: it's that in
> ordinary DML queries, it seldom matters very much whether a
> subexpression is evaluated at parse time versus run time.
> In CREATE TABLE that difference is very in-your-face, so people
> who haven't understood the rules clearly can get burnt.
>
> However, there are certainly other places where it matters,
> such as queries in plpgsql functions.  So I understand your
> reluctance to go on about it in CREATE TABLE.  At the same
> time, I see where David J. is coming from.
>
> Maybe we could have a discussion of this in some single spot,
> and link to it from CREATE TABLE and other relevant places?
> ISTR there is something about it in the plpgsql doco already.
>

+1 to this idea.

Re: Postgresql OOM

[Pantelis Theodosiou]

On Thu, Jun 6, 2024 at 1:25 PM Radu Radutiu  wrote:

> Hello all,
>
> I have a query that forces an out of memory error, where the OS will kill
> the postgresql process.
> The query plan (run immediately after a vacuum analyze) is at
> https://explain.depesz.com/s/ITQI#html .
>
> ...

>
> Any idea on how to identify the problem? I can reproduce it on demand.
> Should I report it pgsql-bugs?
>
> Best regards,
> Radu
>

I am not qualified to answer on the OOM issue but why are you joining the
same table (outputrequest) 4 times (using an identical join condition)?
This essentially does a cross join, if an input_sequence value has say,
1000 related rows in outputrequest, you will be getting 1000^4 rows in the
result set.

  FROM inputrequest t
  LEFT JOIN outputrequest rec_tro
  ON rec_tro.input_sequence = t.input_sequence
  LEFT JOIN inputrequest r
  ON r.originalRequest_id = t.input_sequence
  LEFT JOIN outputrequest rpl_rec_tro
  ON rpl_rec_tro.input_sequence = r.input_sequence
  LEFT JOIN outputrequest rpl_snd_tro
  ON rpl_snd_tro.reply_input_sequence = r.input_sequence
  LEFT JOIN outputrequest snd_tro
  ON snd_tro.reply_input_sequence = t.input_sequence

Re: First draft of PG 17 release notes

[Pantelis Theodosiou]

On Thu, May 9, 2024 at 5:03 AM Bruce Momjian  wrote
>
>
> I welcome feedback.  For some reason it was an easier job than usual.

This looks better if "more case" -> "more cases" :
>  Allow query nodes to be run in parallel in more case (Tom Lane)

Re: ANY_VALUE aggregate

[Pantelis Theodosiou]

On Tue, Dec 6, 2022 at 4:57 AM David G. Johnston
 wrote:
...
>
>
> I'm referring to the query:
>
> select any_value(v order by v) from (values (2),(1),(3)) as vals (v);
> // produces 1, per the documented implementation-defined behavior.
>
> Someone writing:
>
> select any_value(v) from (values (2),(1),(3)) as vals (v) order by v;
>
> Is not presently, nor am I saying, promised the value 1.
>

Shouldn't the 2nd query be producing an error, as it has an implied
GROUP BY () - so column v cannot appear (unless aggregated) in SELECT
and ORDER BY?

Re: Tuples inserted and deleted by the same transaction

[Pantelis Theodosiou]

On Tue, Sep 13, 2022 at 11:04 AM Laurenz Albe  wrote:
>
> On Tue, 2022-09-13 at 11:47 +0300, Nikita Malakhov wrote:
> > On Tue, Sep 13, 2022 at 11:06 AM Laurenz Albe  
> > wrote:
> > > Shouldn't such tuples be considered dead right away, even if the inserting
> > > transaction is still active?  That would allow cleaning them up even 
> > > before
> > > the transaction is done.
> > >
> > > There is this code in HeapTupleSatisfiesVacuumHorizon:
> > >
> > > else if 
> > > (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetRawXmin(tuple)))
> > > {
> > > [...]
> > > /* inserted and then deleted by same xact */
> > > if 
> > > (TransactionIdIsCurrentTransactionId(HeapTupleHeaderGetUpdateXid(tuple)))
> > > return HEAPTUPLE_DELETE_IN_PROGRESS;
> > >
> > > Why HEAPTUPLE_DELETE_IN_PROGRESS and not HEAPTUPLE_DEAD?
> >
> > Please correct me if I'm wrong, despite tuples being inserted and deleted 
> > by the same
> > transaction - they are visible inside the transaction and usable by it, so 
> > considering them
> > dead and cleaning up during execution is a bad idea until the transaction 
> > is ended.
>
> But once they are deleted or updated, even the transaction that created them 
> cannot
> see them any more, right?

Forgive me if this is not related but if there is a savepoint between
the insertion and deletion, wouldn't it be possible for the
transaction to recover the deleted tuples?

Best regards
Pantelis Theodosiou

Re: PostgreSQL 15 beta 2 release announcement draft

[Pantelis Theodosiou]

> Upgrading to PostgreSQL 15 Beta 2
> -
>
> To upgrade to PostgreSQL 15 Beta 2 from an earlier version of PostgreSQL,
> you will need to use a strategy similar to upgrading between major versions of
> PostgreSQL (e.g. `pg_upgrade` or `pg_dump` / `pg_restore`). For more
> information, please visit the documentation section on
> [upgrading](https://www.postgresql.org/docs/15/static/upgrading.html).

Is the major version upgrade still needed if they are upgrading from 15 Beta 1?

Re: Reference column alias for common expressions

[Pantelis Theodosiou]

On Tue, May 24, 2022 at 4:12 PM Wood May  wrote:
>
> Hi,
> Some databases (like Teradata) support the following syntax:
>
> select col1, col2*20 as col2_1, col2_1*200 as col3_1 from your_table;
>
> The last element in the target list can refer the second one using its 
> alias.
>
> This feature is similar to some programming languages (like Lisp)'s let*.

I think this is incompatible with SQL semantics.

>
> For Postgres, it seems the only way is to write a subquery and then a new 
> target list.

Another option is to use LATERAL subqueries, eg

select t.col1, level1.col2_1, level2.col3_1
from your_table as t
  lateral join
(select t.col2*20 as col2_1) as level1 on true
  lateral join
(select level1.col2_1*200 as col3_1) as level2 on true ;

>
> Will Postgres plan to support this feature?
>
> Thanks a lot!

Regards
 Pantelis Theodosiou

Re: Idea: Avoid JOINs by using path expressions to follow FKs

[Pantelis Theodosiou]

On Sat, Mar 27, 2021 at 8:28 PM Joel Jacobson  wrote:

> Hi,
>
> The database Neo4j has a language called "Cypher" where one of the key
> selling points is they "don’t need join tables".
>
> Here is an example from
> https://neo4j.com/developer/cypher/guide-sql-to-cypher/
>
> SQL:
>
> SELECT DISTINCT c.company_name
> FROM customers AS c
> JOIN orders AS o ON c.customer_id = o.customer_id
> JOIN order_details AS od ON o.order_id = od.order_id
> JOIN products AS p ON od.product_id = p.product_id
> WHERE p.product_name = 'Chocolade';
>
> Neo4j's Cypher:
>
> MATCH (p:product
> {product_name:"Chocolade"})<-[:PRODUCT]-(:order)<-[:PURCHASED]-(c:customer)
> RETURN distinct c.company_name;
>
> Imagine if we could simply write the SQL query like this:
>
> SELECT DISTINCT od.order_id.customer_id.company_name
> FROM order_details AS od
> WHERE od.product_id.product_name = 'Chocolade';
>
> I took the inspiration for this syntax from SQL/JSON path expressions.
>
> Since there is only a single foreign key on the order_details.order_id
> column,
> we would know how to resolve it, i.e. to the orders table,
> and from there we would follow the customer_id column to the customers
> table,
> where we would finally get the company_name value.
>
> In the where clause, we would follow the order_details's product_id column
> to the products table, to filter on product_name.
>
> If there would be multiple foreign keys on a column we try to follow,
> the query planner would throw an error forcing the user to use explicit
> joins instead.
>
> I think this syntactic sugar could save a lot of unnecessary typing,
> and as long as the column names are chosen wisely,
> the path expression will be just as readable as the manual JOINs would be.
>
> Thoughts?
>
> /Joel
>

Just my 2c. The idea is nice but:

1. It is changing the FROM clause and the (size of the) intermediate result
set. While in your example query there is no difference, you'd get
different results if it was something like

SELECT p.product_name, COUNT(*)
FROM ...  (same joins)
GROUP BY p.product_name

2. If you want many columns in the SELECT list, possibly form many tables,
you'll need to repeated the expressions. i.e. how you propose  to write
this without repeating the link expressions?

SELECT p.product_name, p.price, p.category, c.company_name, c.address
...

3. SQL already provides methods to remove the join "noise", with JOIN USING
(columns) when joining column have matching names and with NATURAL JOIN
(with extreme care).

Finally, extending the specs in this novel way might put Postgres in a
different path from the SQL specs in the future, especially if they have
plans to add functionality for graph queries.

Best regards
Pantelis Theodosiou

Re: [PATCH] Allow multiple recursive self-references

[Pantelis Theodosiou]

On Tue, Mar 23, 2021 at 1:03 PM Denis Hirn 
wrote:

>
> Hey everyone,
>
> As you know, Postgres currently supports SQL:1999 recursive common table
> expressions, using WITH RECURSIVE. However, Postgres does not allow more
> than
> one recursive self-reference in the recursive term. This restriction seems
> to be
> unnecessary.
>
> In this mail, I'd like to propose a patch that removes this restriction,
> and
> therefore allows the use of multiple self-references in the recursive term.
> After the patch:
>
> WITH RECURSIVE t(n) AS (
> VALUES(1)
>   UNION ALL
> SELECT t.n+f.n
> FROM t, t AS f
> WHERE t.n < 100
> ) SELECT * FROM t;
>
>   n
> -
>1
>2
>4
>8
>   16
>   32
>   64
>  128
> (8 rows)
>
> This feature deviates only slightly from the current WITH RECURSIVE, and
> requires very little changes (~10 loc). Any thoughts on this?
>
> --
> Denis Hirn
>

I am not at all sure what the standard says about such recursion but it
looks like the two t's are treated in your patch as the same incarnation of
the table, not as a cross join of two incarnations. The natural result I
would expect from a this query would be all numbers from 1 to 198 (assuming
that the query is modified to restrict f.n and that UNION ALL is converted
to UNION to avoid infinite recursion).

I don't think any other DBMS has implemented this, except MariaDB. Tested
here:
https://dbfiddle.uk/?rdbms=mariadb_10.5=565c22771fdfc746e05808a7da7a205f

SET  @@standard_compliant_cte=0;
WITH RECURSIVE t(n) AS (
SELECT 1
  UNION -- ALL
SELECT t.n + f.n
FROM t, t AS f
WHERE t.n < 4 AND f.n < 4
) SELECT * FROM t;

Result:

> |  n |
> | -: |
> |  1 |
> |  2 |
> |  3 |
> |  4 |
> |  5 |
> |  6 |

Best regards
Pantelis Theodosiou

Re: [PATCH] Support empty ranges with bounds information

[Pantelis Theodosiou]

On Tue, Mar 2, 2021 at 4:57 PM Mark Dilger 
wrote:

>
>
> > On Mar 2, 2021, at 8:51 AM, Pantelis Theodosiou 
> wrote:
> >
> >
> >
> > On Tue, Mar 2, 2021 at 3:28 PM Mark Dilger 
> wrote:
> >
> >
> > > On Mar 2, 2021, at 5:20 AM, Joel Jacobson  wrote:
> > >
> > > it's currently not possible to create an empty range with bounds
> information.
> > >
> > > This patch tries to improve the situation by keeping the bounds
> information,
> > > and allow accessing it via lower() and upper().
> > >
> > > No other semantics have been changed.
> > > All tests passes without any changes.
> >
> > I recall this issue of empty ranges not keeping any bounds information
> being discussed back when range types were developed, and the design choice
> was intentional.  Searching the archives for that discussion, I don't find
> anything, probably because I'm not searching for the right keywords.
> Anybody have a link to that discussion?
> >
> > —
> > Mark Dilger
> > EnterpriseDB: http://www.enterprisedb.com
> > The Enterprise PostgreSQL Company
> >
> >
> >  Marc, perhaps you were referring to this discussion?
> >
> https://www.postgresql.org/message-id/4d5534d002250003a...@gw.wicourts.gov
>
> Yes, I believe so.  Thank you for the link.
>

Welcome. Also this message, where I found the link and has an overview of
the different views at the time (and more links):

https://www.postgresql.org/message-id/1299865026.3474.58.camel%40jdavis

On Fri, 2011-03-11 at 08:37 -0500, Bruce Momjian wrote:
> > Where are we on this? The options are: 1. Rip out empty ranges. Several
> people have been skeptical of their
> usefulness, but I don't recall anyone directly saying that they should
> be removed. Robert Haas made the point that range types aren't closed
> under UNION:
> http://archives.postgresql.org/pgsql-hackers/2011-02/msg01045.php So the
> additional nice mathematical properties provided by empty ranges
> are not as important (because it wouldn't be perfect anyway). 2. Change
> the semantics. Erik Rijkers suggested that we define all
> operators for empty ranges, perhaps using NULL semantics:
> http://archives.postgresql.org/pgsql-hackers/2011-02/msg00942.php And
> Kevin Grittner suggested that there could be discrete ranges of zero
> length yet a defined starting point:
> http://archives.postgresql.org/pgsql-hackers/2011-02/msg01042.php 3.
> Leave empty ranges with the existing "empty set" semantics. Nathan
> Boley made a good point here:
> http://archives.postgresql.org/pgsql-hackers/2011-02/msg01108.php Right
> now it's #3, and I lean pretty strongly toward keeping it. Without
> #3, people will get confused when fairly simple operations fail in a
> data-dependent way (at runtime). With #3, people will run into problems
> only in situations where it is fairly dubious to have an empty range
> anyway (and therefore likely a real error), such as finding ranges "left
> of" an empty range. Otherwise, I'd prefer #1 to #2. I think #2 is a bad
> path to take, and
> we'll end up with a lot of unintuitive and error-prone operators. Regards,
> Jeff Davis

Re: [PATCH] Support empty ranges with bounds information

[Pantelis Theodosiou]

On Tue, Mar 2, 2021 at 3:28 PM Mark Dilger 
wrote:

>
>
> > On Mar 2, 2021, at 5:20 AM, Joel Jacobson  wrote:
> >
> > it's currently not possible to create an empty range with bounds
> information.
> >
> > This patch tries to improve the situation by keeping the bounds
> information,
> > and allow accessing it via lower() and upper().
> >
> > No other semantics have been changed.
> > All tests passes without any changes.
>
> I recall this issue of empty ranges not keeping any bounds information
> being discussed back when range types were developed, and the design choice
> was intentional.  Searching the archives for that discussion, I don't find
> anything, probably because I'm not searching for the right keywords.
> Anybody have a link to that discussion?
>
> —
> Mark Dilger
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
 Marc, perhaps you were referring to this discussion?
https://www.postgresql.org/message-id/4d5534d002250003a...@gw.wicourts.gov

Re: Serializable wrong?

[Pantelis Theodosiou]

On Fri, Jun 12, 2020 at 6:58 PM Joshua Drake  wrote:

> -Hackers,
>
> I came across this today [1], "
> 3 Results
>
> In most respects, PostgreSQL behaved as expected: both read uncommitted
> and read committed prevent write skew and aborted reads. We observed no
> internal consistency violations. However, we have two surprising results to
> report. The first is that PostgreSQL’s “repeatable read” is weaker than
> repeatable read, at least as defined by Berenson, Adya, Bailis, et al. This
> is not necessarily wrong: the ANSI SQL standard is ambiguous. The second
> result, which is definitely wrong, is that PostgreSQL’s “serializable”
> isolation level isn’t serializable: it allows G2-item during normal
> operation. "
>
> Thanks!
>
> JD
>
> 1. https://jepsen.io/analyses/postgresql-12.3
>

Yes, this has been reported and is under discussion in pgsql-bugs list:

https://www.postgresql.org/message-id/db7b729d-0226-d162-a126-8a8ab2dc4443%40jepsen.io

Re: PostgreSQL 13 Beta 1 Release Announcement Draft

[Pantelis Theodosiou]

On Thu, May 21, 2020 at 3:20 PM Pantelis Theodosiou 
wrote:

> Congrats to all for the release of a new major version!
>
> Two questions:
> - Why is VACUUM together with FETCH FIRST WITH TIES, CREATE TABLE LIKE,
> ALTER VIEW, ALTER TABLE, etc in Utility Commands section?
>   Shouldn't there be a separate section for SQL changes? (or keep one
> section but rename the Utility to include all?)
>
> > Add FOREIGN to ALTER statements, if appropriate (Luis Carril)
>
> > WHAT IS THIS ABOUT?
> - The "WHAT IS THIS ABOUT?" should be removed, in my opinion.
>
> Again, congrats for another release of the best database in the world.
>
> Pantelis Theodosiou
>
> On Thu, May 21, 2020 at 12:44 PM Jonathan S. Katz 
> wrote:
>
>>
>> Thanks for the suggestions. I ended up incorporating all of them.
>>
>> Stay tuned for the release...
>>
>> Jonathan
>>
>>
Apologies, I realized a minute too late that my comments are about the
Release Notes and not the Announcement.
However, since the link to Notes makes them no visible to more eyes, they
could be checked again.

Pantelis Theodosiou

Re: PostgreSQL 13 Beta 1 Release Announcement Draft

[Pantelis Theodosiou]

Congrats to all for the release of a new major version!

Two questions:
- Why is VACUUM together with FETCH FIRST WITH TIES, CREATE TABLE LIKE,
ALTER VIEW, ALTER TABLE, etc in Utility Commands section?
  Shouldn't there be a separate section for SQL changes? (or keep one
section but rename the Utility to include all?)

> Add FOREIGN to ALTER statements, if appropriate (Luis Carril)

> WHAT IS THIS ABOUT?
- The "WHAT IS THIS ABOUT?" should be removed, in my opinion.

Again, congrats for another release of the best database in the world.

Pantelis Theodosiou

On Thu, May 21, 2020 at 12:44 PM Jonathan S. Katz 
wrote:

> Hi John,
>
> On 5/21/20 12:12 AM, John Naylor wrote:
> > Hi Jon,
> >
> > I noticed a couple minor inconsistencies:
> >
> > ".datetime" -> elsewhere functions are formatted as `.datetime()`
> >
> > libpq -> `libpq`
> >
> > The link to the release notes on its own line is the same as the
> > inline link, if that makes sense. In other places with links on their
> > own line, the full URL is in the link text.
> >
> > Also, for "indexes that contain many repeat values", "repeated" might
> > sound better here. It's one of those things that jumped out at me at
> > first reading, but when trying both in my head, it seems ok.
> >
> > Regarding "streaming `pg_basebackup`s", I'm used to the general term
> > "base backups" in this usage, which seems a  distinct concept from the
> > name of the invoked command.
>
> Thanks for the suggestions. I ended up incorporating all of them.
>
> Stay tuned for the release...
>
> Jonathan
>
>

Re: [PATCH] Implement INSERT SET syntax

[Pantelis Theodosiou]

On Thu, Nov 14, 2019 at 9:20 PM Tom Lane  wrote:

> Gareth Palmer  writes:
> >> On 19/08/2019, at 3:00 AM, Tom Lane  wrote:
> >> Perhaps the way to resolve Peter's objection is to make the syntax
> >> more fully like UPDATE:
> >> INSERT INTO target SET c1 = x, c2 = y+z, ... FROM
> tables-providing-x-y-z
> >> (with the patch as-submitted corresponding to the case with an empty
> >> FROM clause, hence no variables in the expressions-to-be-assigned).
>
> > Thanks for the feedback. Attached is version 3 of the patch that makes
> > the syntax work more like an UPDATE statement when a FROM clause is used.
>
> Since nobody has objected to this, I'm supposing that there's general
> consensus that that design sketch is OK, and we can move on to critiquing
> implementation details.  I took a look, and didn't like much of what I saw.
>
> ...
>
> I'm setting this back to Waiting on Author.
>
> regards, tom lane
>
>
>
Regarding syntax and considering that it makes INSERT look like UPDATE:
there is another difference between INSERT and UPDATE. INSERT allows SELECT
with ORDER BY and OFFSET/LIMIT (or FETCH FIRST), e.g.:

INSERT INTO t (a,b)
SELECT a+10. b+10
FROM t
ORDER BY a
LIMIT 3;

But UPDATE doesn't. I suppose the proposed behaviour of INSERT .. SET will
be the same as standard INSERT. So we'll need a note for the differences
between INSERT/SET and UPDATE/SET syntax.

On a related not, column aliases can be used in ORDER BY, e.g:

insert into t (a, b)
select
a + 20,
b - 2 * a as f
from t
order by f desc
limit 3 ;

Would that be expressed as follows?:

insert into t
set
a = a + 20,
b = b - 2 * a as f
from t
order by f desc
limit 3 ;

Best regards,
Pantelis Theodosiou

Re: PostgreSQL 12 Beta 1 press release draft

[Pantelis Theodosiou]

On Thu, May 23, 2019 at 4:36 PM Pantelis Theodosiou 
wrote:

>
> On Thu, May 23, 2019 at 1:01 PM Jonathan S. Katz 
> wrote:
>
>> On 5/23/19 1:45 AM, David Rowley wrote:
>> > On Thu, 23 May 2019 at 15:31, Jonathan S. Katz 
>> wrote:
>> >> Attached is
>> >> v3 of the patch, along with a diff.
>> >
>> > Minor details, but this query is not valid:
>> >
>> >> WITH c AS MATERIALIZED (
>> >>   SELECT * FROM a WHERE a.x % 4
>> >> )
>> >> SELECT * FROM c JOIN d ON d.y = a.x;
>> >
>> > a.x % 4 is not a boolean clause, and "a" is not in the main query, so
>> > a.x can't be referenced there.
>>
>> ...that's the only gotcha I'm actually embarrassed about. Fixed.
>>
>>
> The   ON d.y = a.x  still needs to be changed to ON d.y = c.x
>
> Pantelis
>

Another minor point in the sentence  "... which is currently is ...":

> In PostgreSQL 12, the storage interface that is used by default is the
heap access method, which is currently is the only built-in method.

But I forgot the most important. Thank you for the new version and all the
work that has gone into it!

Re: PostgreSQL 12 Beta 1 press release draft

[Pantelis Theodosiou]

On Thu, May 23, 2019 at 1:01 PM Jonathan S. Katz 
wrote:

> On 5/23/19 1:45 AM, David Rowley wrote:
> > On Thu, 23 May 2019 at 15:31, Jonathan S. Katz 
> wrote:
> >> Attached is
> >> v3 of the patch, along with a diff.
> >
> > Minor details, but this query is not valid:
> >
> >> WITH c AS MATERIALIZED (
> >>   SELECT * FROM a WHERE a.x % 4
> >> )
> >> SELECT * FROM c JOIN d ON d.y = a.x;
> >
> > a.x % 4 is not a boolean clause, and "a" is not in the main query, so
> > a.x can't be referenced there.
>
> ...that's the only gotcha I'm actually embarrassed about. Fixed.
>
>
The   ON d.y = a.x  still needs to be changed to ON d.y = c.x

Pantelis

Re: Constraint documentation

[Pantelis Theodosiou]

On Thu, Aug 9, 2018 at 10:32 PM, Alvaro Herrera 
wrote:

> On 2018-Aug-07, Lætitia Avrot wrote:
>
> > Hi Peter,
> >
> > I understand what you're pointing at and I agree that it could be a good
> > thing to be able to dump/restore a table without problem.
> >
> > My point was that check constraints weren't supposed to be used that way
> > theorically (or maybe i'm mistaken ?) so I thought maybe we should just
> > inform the user that this kind of use of a check constraint is a misuse
> of
> > that feature.
>
> Tom Lane pointed out in another thread that the SQL standard lists
> feature F673 "Reads SQL-data routine invocations in CHECK constraints"
> which permits CHECK constraints to examine tables, so saying "you're not
> supposed to do this", while correct from a Postgres perspective, would
> be short-sighted ISTM, because we will make ourselves liars as soon as
> we implement the feature.
>
> I agree that we should point this out in *some* way, just not sure how.
> Maybe something like "Postgres does not currently support CHECK
> constraints containing queries, therefore we recommend to avoid them."
> I would not mention pg_dump by name, just say dumps may not restore
> depending on phase of moon.
>
> (BTW I'm not sure of the term "other tables".  You could have a query
> that references the same table ...)
>
> --
> Álvaro Herrerahttps://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

I like this:

> "Postgres does not currently support CHECK constraints containing
queries, therefore we recommend to avoid them."

Perhaps adding:

> CHECK constraints are currently meant to be used as *row constraints*
only.
> Use - if possible - UNIQUE or EXCLUDE constraints. for constraints that
involve many or all rows of a table,
> and FOREIGN KEY constraints for cross table constraints.
> More complex constraints will be available when ASSERTION are implemented.

And then adding some warning about using functions in CHECK constraints to
bypass current limitations.

Pantelis Theodsoiou

Re: Windows build broken starting at da9b580d89903fee871cf54845ffa2b26bda2e11

[Pantelis Theodosiou]

On Tue, May 15, 2018 at 6:29 PM, Tom Lane  wrote:

> Mark Dilger  writes:
>
> > I don't have a strong opinion on that.  I could also look to upgrade
> > to a newer version.  Generally, I try to build using the oldest
> > supported version rather than the newest.  What is the next oldest
> > working test machine you have?
>
> thrips is described as running VS2010, though I'm not sure how to verify
> that it's not been updated.  The make log shows
>
>   c:\Program Files (x86)\Microsoft Visual Studio 10.0\VC\bin\AMD64\CL.exe
>
> but is "10.0" the same thing as "2010"?
>
> regards, tom lane
>
>
According to Wikipedia, yes:
https://en.wikipedia.org/wiki/Microsoft_Visual_Studio

(although the 10s are only by coincidence, 11.0 is VS 2012, ...)

Re: [HACKERS] FOSDEM PGDay_2018_Developer_Meeting notes

[Pantelis Theodosiou]

On Tue, Feb 6, 2018 at 10:07 AM, Stephen Frost  wrote:

>
> That was also what seemed to be the consensus coming out of the FOSDEM
> Developer meeting (notes here:
> https://wiki.postgresql.org/wiki/FOSDEM/PGDay_2018_Developer_Meeting).
>
>
In the notes there is this, which confused me:


> SIDE TOPIC:
>
> Release date for PostgreSQL 13 agreed: Friday 13th September 2019!!


Isn't Postgres 12 to be released in 2019? And 13 in 2020?

Re: Add RANGE with values and exclusions clauses to the Window Functions

[Pantelis Theodosiou]

On Sun, Feb 4, 2018 at 6:10 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Oliver Ford <ojf...@gmail.com> writes:
> > [ 0001-window-frame-v13.patch ]
>
> I've been hacking on this all week (with breaks for release notes) and
> have gotten it into a state that I think is close to committable.
>
> There was quite a lot I didn't like about the patch initially, notably
> that the interaction with operator classes/families was done all wrong.
> The idea is to add one support function per opclass, not jam them all
> into one opclass that breaks every rule for B-tree opclasses.  For one
> reason, with this approach there's no chance of dealing with non-default
> sort orders imposed by non-default opclasses.  (As a concrete example,
> suppose that we have two btree opclasses for complex numbers, one that
> sorts by real part and one that sorts by imaginary part.  You can write
> a well-defined in_range function for each of these, but one of them has
> to increment the real part and the other the imaginary part.)  I whacked
> that around and also wrote the missing documentation for the API spec
> for in_range functions.  The path of least resistance was to dump it
> into the nbtree/README text file, which I'm not that satisfied with;
> probably it should go in the main SGML docs, but I did not find a good
> place to put it.
>
> I also really didn't like the implementation you'd chosen in
> nodeWindowAgg.c to scan the entire partition and build an array of peer
> group lengths.  That risks running OOM with a large partition.  Even if
> the array doesn't lead to OOM, the tuplestore will spill to disk with
> nasty performance consequences.  We should try to ensure that the
> tuplestore needn't get larger than the frame, so that well-written queries
> with narrow frames can execute without spilling to disk.  So I rewrote
> that using an idea that had been speculated about in the original
> comments, but nobody had gotten to yet: add some more read pointers to
> track the frame boundaries, and advance them as needed.  I'm not really
> sure if this ends up as more or few row comparisons than the other way,
> but in any case it uses a fixed amount of memory, which is good.
>
> Also, the last patch's reimplementation of WinGetFuncArgInFrame isn't
> right: AFAICS, it results in any "relpos" that would point to a row
> in the exclusion range returning the row just after/before that range,
> which is already wrong if the exclusion range is more than one row,
> plus it doesn't renumber the rows beyond the exclusion.  The behavior
> we want is that the frame rows surviving after exclusion should appear
> consecutively numbered.  (This could be exposed with some tests using
> nth_value.)  I think the attached rewrite gets this right.  Also, punting
> entirely on the set-mark problem for SEEK_TAIL cases doesn't satisfy me,
> for the same reason as above that we don't want the tuplestore to bloat.
> What I did below is to set the mark at the frame start, which at least
> gives an opportunity for efficient queries.
>
> I hacked around on various other things too, for instance the behavior
> for null values in RANGE mode didn't seem to be per spec.
>
> I'm reasonably happy with all the code now, though surely it could use
> another look by someone else.  I've not yet reviewed the docs (other than
> the implementor-oriented details I added), nor have I really looked at the
> test cases.  I do have a couple suggestions on the test cases: for one,
> rather than duplicating the same window definition N times in each query,
> use one WINDOW clause and reference it with "OVER windowname".  Also,
> adding a bunch of columns of different types to a single table seems like
> a messy and not easily extensible way of testing different data types.
> I'd suggest leaving the existing table alone and adding a new test table
> per additional data type you want to test, so that there's an easy
> template for testing future additions of more in_range support.
>
> BTW, something I've not done here but am strongly tempted to do is
> run around and change all the uses of "RANGE value PRECEDING/FOLLOWING"
> terminology to, say, "RANGE offset PRECEDING/FOLLOWING".  "value" is
> just way too generic a term for this situation, making documentation
> confusing, plus you end up contorting sentences to avoid constructions
> like "value of the value".  I'm not wedded to "offset" if somebody's got a
> better word, but let's try to pick something more specific than "value".
> (In the ROWS and GROUPS cases, maybe write "count"?  Not entirely sure
> what to do for text that's trying to address all three cases, though.)
>
>
What about "extent_size" or just "size"? I see the SQL spec refers to
"preceding or following size" in an error message: ("data exception —
invalid preceding or following size in window function" )

Best regards
Pantelis Theodosiou

Re: AS OF queries

[Pantelis Theodosiou]

On Wed, Dec 20, 2017 at 4:26 PM, Tom Lane  wrote:

> Peter Eisentraut  writes:
> > On 12/20/17 10:29, Tom Lane wrote:
> >> Please say that's just an Oracle-ism and not SQL standard, because it's
> >> formally ambiguous.
>
> > The SQL standard syntax appears to be something like
>
> > "tablename" [ AS OF SYSTEM TIME 'something' ] [ [ AS ] "alias" ]
>
> > That's not going to be fun to parse.
>

Examples from DB2 documentation (which may be closer to the standard):

SELECT coverage_amt
FROM policy FOR SYSTEM_TIME AS OF '2010-12-01'
WHERE id = ;


SELECT count(*)
FROM policy FOR SYSTEM_TIME FROM '2011-11-30'
  TO '-12-30'
WHERE vin = 'A';


So besides AS .. AS , it could also be  FROM .. FROM


> Bleah.  In principle we could look two tokens ahead so as to recognize
> "AS OF SYSTEM", but base_yylex is already a horrid mess with one-token
> lookahead; I don't much want to try to extend it to that.
>
> Possibly the most workable compromise is to use lookahead to convert
> "AS OF" to "AS_LA OF", and then we could either just break using OF
> as an alias, or add an extra production that allows "AS_LA OF" to
> be treated as "AS alias" if it's not followed by the appropriate
> stuff.
>
> It's a shame that the SQL committee appears to be so ignorant of
> standard parsing technology.
>
> regards, tom lane
>
>

Re: [HACKERS] Secondary index access optimizations

[Pantelis Theodosiou]

On Tue, Sep 5, 2017 at 9:10 AM, Konstantin Knizhnik <
k.knizh...@postgrespro.ru> wrote:

>
>
> On 05.09.2017 04:02, Amit Langote wrote:
>
> Like Thomas, I'm not so sure about the whole predtest.c patch.  The core
> logic in operator_predicate_proof() should be able to conclude that, say,
> k < 21 is implied by k <= 20, which you are trying to address with some
> special case code.  If there is still problem you think need to be fixed
> here, a better place to look at would be somewhere around get_btree_test_op().
>
>
> Frankly speaking I also do not like this part of my patch.
> I will be pleased if you or somebody else can propose better solution.
> I do not understand how get_btree_test_op() can help here.
>
> Yes, k < 21 is implied by k <= 20. It is based on generic properties of <
> and  <= operators.
> But I need to proof something different: having table partition constraint
> (k < 21) I want to remove predicate (k <= 20) from query.
> In other words,  operator_predicate_proof() should be able to conclude
> that (k <= 20) is implied by (k < 21).
> But it is true only for integer types, not for floating point types. And
> Postgres operator definition
> doesn't provide some way to determine that user defined type is integer
> type: has integer values for which such conclusion is true.
>
> Why I think that it is important? Certainly, it is possible to rewrite
> query as (k < 21) and no changes in operator_predicate_proof() are needed.
> Assume the most natural use case: I have some positive integer key and I
> wan to range partition table by such key, for example with interval 1.
> Currently standard PostgreSQL partitioning mechanism requires to specify
> intervals with open high boundary.
> So if I want first partition to contain interval [1,1], second -
> [10001,20001],... I have to create partitions in such way:
>
> create table bt (k integer, v integer) partition by range (k);
> create table dt1 partition of bt for values from (1) to (10001);
> create table dt2 partition of bt for values from (10001) to (20001);
> ...
>
> If I want to write query inspecting data of the particular partition, then
> most likely I will use BETWEEN operator:
>
> SELECT * FROM t WHERE k BETWEEN 1 and 1;
>
> But right now operator_predicate_proof()  is not able to conclude that
> predicate (k BETWEEN 1 and 1) transformed to (k >= 1 AND k <= 1) is
> equivalent to (k >= 1 AND k < 10001)
> which is used as partition constraint.
>
> Another very popular use case (for example mentioned in PostgreSQL
> documentation of partitioning: https://www.postgresql.org/
> docs/10/static/ddl-partitioning.html)
> is using date as partition key:
>
> CREATE TABLE measurement (
> city_id int not null,
> logdate date not null,
> peaktempint,
> unitsales   int
> ) PARTITION BY RANGE (logdate);
>
>
> CREATE TABLE measurement_y2006m03 PARTITION OF measurement
> FOR VALUES FROM ('2006-03-01') TO ('2006-04-01')
>
>
> Assume that now I want to get measurements for March:
>
> There are three ways to write this query:
>
> select * from measurement where extract(month from logdate) = 3;
> select * from measurement where logdate between '2006-03-01' AND
> '2006-03-31';
> select * from measurement where logdate >= '2006-03-01' AND logdate  <
> '2006-04-01';
>
> Right now only for the last query optimal query plan will be constructed.
>

Perhaps the relative pages (about partitioning and optimization) should
mention to avoid BETWEEN and using closed-open checks, as the last query.

Dates are a perfect example to demonstrate that BETWEEN shouldn't be used,
in my opinion. Dates (and timestamps) are not like integers as they are
often used with different levels of precisions, day, month, year, hour,
minute, second, etc. (month in your example). Constructing the correct
expressions for the different precisions can be a nightmare with BETWEEN
but very simple with >= and < (in the example: get start_date,
'2006-03-01', and add one month).

So, just my 2c, is it worth the trouble to implement this feature
(conversion of (k<21) to (k<=20) and vice versa) and how much work would it
need for all data types that are commonly used for partitioning?



> Unfortunately my patch is not covering date type.
>
> --
> Konstantin Knizhnik
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>