Re: PostgreSQL vs SQL/XML Standards

po 29. 10. 2018 v 11:05 odesílatel Pavel Stehule napsal: > > (It would not be exactly overloading, because of the special sugared >>> syntax known to the parser, but it could look like overloading, and be >>> intuitive to the user.) >>> >>> If you have convenient access to Oracle to check compati

Re: Ordered Partitioned Table Scans

On 29 October 2018 at 13:44, David Rowley wrote: > v2 of the patch is attached. I've not had time yet to give it a > throughout post write review, but on first look it seems okay. Added to the November 'fest. https://commitfest.postgresql.org/20/1850/ -- David Rowley http://

Re: [HACKERS] Exclude schema during pg_restore

On 12/09/2018 19:12, Michael Banck wrote: >> I added the new option to the help output in pg_restore. > > I noticed this part of the help text does not mention `-N' when I think > it should: > > |The options -I, -n, -P, -t, -T, and --section can be combined and specified > |multiple times to sele

Re: PostgreSQL vs SQL/XML Standards

>> I have a access to too old 11.2 Oracle. There I had to modify query >> because there is not boolean type. I replaced bool by int, but I got a >> error >> ORA-19224:XPTY-004 .. expected node()*, got xs:string - it doesn't work >> with/without string() wrappings. >> >The problem is in last line -

Re: PostgreSQL vs SQL/XML Standards

po 29. 10. 2018 v 10:11 odesílatel Pavel Stehule napsal: > Hi > > čt 25. 10. 2018 v 21:47 odesílatel Alvaro Herrera < > alvhe...@2ndquadrant.com> napsal: > >> On 2018-Oct-25, Pavel Stehule wrote: >> >> > I am thinking so I can fix some issues related to XMLTABLE. Please, >> send me >> > more exam

Re: [HACKERS] Exclude schema during pg_restore

Am Montag, den 29.10.2018, 11:35 +0100 schrieb Peter Eisentraut: > On 12/09/2018 19:12, Michael Banck wrote: > Committed to 10, 11, and master. Thanks! Michael -- Michael Banck Projektleiter / Senior Berater Tel.: +49 2166 9901-171 Fax: +49 2166 9901-100 Email: michael.ba...@credativ.de cred

Re: MERGE SQL statement for PG12

Hi Pavan, On 10/29/2018 10:23 AM, Pavan Deolasee wrote: ... Thanks for keeping an eye on the patch. I've rebased the patch against the current master. A new version is attached. Thanks, Pavan It's good to see the patch moving forward. What are your thoughts regarding things that need to b

Re: INSTALL file

On 28.10.18 20:39, Andrew Dunstan wrote: On 10/28/2018 08:13 AM, Andreas 'ads' Scherbaum wrote: Hello, while working with Google Code-In students, there is one task: "clone PostgreSQL from git repository, and build from source". This brought up an interesting problem: the README refers to

RE: [Proposal] Add accumulated statistics for wait event

Hi, Is DBA really able to solve bottlenecks with sampling? What I would like to say is that if we have information on the number of wait events and the wait time(like other DB), we can investigate more easily. Yes you will be able to solve bottlenecks with sampling. In interactive mode, a 1s i

pgadmin4 and scram

I am using pgadmin4 version 3.4 with PG 11.0 and I get this error when I try to connect with scram authorization: User "myuser" does not have a valid SCRAM verifier. How do I get around this? Regards, Michael Vitale

Re: shared-memory based stats collector

Kyotaro HORIGUCHI wrote: > This is more saner version of previous v5-0008, which didn't pass > regression test. v6-0008 to v6-0010 are attached and they are > applied on top of v5-0001-0007. > > - stats collector has been removed. > > - modified dshash further so that deletion is allowed during

Re: pg_promote not marked as parallel-restricted in pg_proc.dat

Laurenz Albe writes: > Yes, it should be PARALLEL RESTRICTED or PARALLEL UNSAFE, but it won't matter > much in practice which of the two we choose. I'd vote for PARALLEL UNSAFE myself. Otherwise you have to ask questions about whether it's really safe to do this while parallel workers are doing

Re: FETCH FIRST clause WITH TIES option

> "Tomas" == Tomas Vondra writes: > On 10/26/2018 12:28 PM, Surafel Temesgen wrote: >> hello , >> >> The WITH TIES keyword is sql standard that specifies any peers of >> retained rows to retained in the result set too .which means >> according to ordering key the result set can inclu

date_trunc() in a specific time zone

A use case that I see quite a lot of is needing to do reports and other calculations on data per day/hour/etc but in the user's time zone. The way to do that is fairly trivial, but it's not obvious what it does so reading queries becomes just a little bit more difficult. Attached is a patch to cr

Re: date_trunc() in a specific time zone

On 10/29/2018 04:18 PM, Vik Fearing wrote: A use case that I see quite a lot of is needing to do reports and other calculations on data per day/hour/etc but in the user's time zone. The way to do that is fairly trivial, but it's not obvious what it does so reading queries becomes just a little b

Re: PostgreSQL vs SQL/XML Standards

On 10/29/18 6:40 AM, Thomas Kellerer wrote: > That line seems to be valid - but you need to pass an XMLTYPE value, > not a VARCHAR > > https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=21cdf890a26e97fa8667b2d6a960bd33 Oh, of course! Thank you. I had forgotten pass the context item as explicitly an XM

Re: date_trunc() in a specific time zone

Andreas Karlsson writes: > Hm, I am not sure if I see any major win from writing > date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') > instead of > date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE > 'Australia/Sydney') The latter would give you timest

Re: date_trunc() in a specific time zone

On 29/10/2018 16:36, Tom Lane wrote: > Andreas Karlsson writes: >> Hm, I am not sure if I see any major win from writing >> date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') >> instead of >> date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE >> 'Australi

Re: date_trunc() in a specific time zone

On 29/10/2018 16:26, Andreas Karlsson wrote: > On 10/29/2018 04:18 PM, Vik Fearing wrote: >> A use case that I see quite a lot of is needing to do reports and other >> calculations on data per day/hour/etc but in the user's time zone.  The >> way to do that is fairly trivial, but it's not obvious w

Re: FETCH FIRST clause WITH TIES option

On 10/29/2018 04:17 PM, Andrew Gierth wrote: "Tomas" == Tomas Vondra writes: > On 10/26/2018 12:28 PM, Surafel Temesgen wrote: >> hello , >> >> The WITH TIES keyword is sql standard that specifies any peers of >> retained rows to retained in the result set too .which means >> accor

Re: date_trunc() in a specific time zone

On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing wrote: > On 29/10/2018 16:26, Andreas Karlsson wrote: > > On 10/29/2018 04:18 PM, Vik Fearing wrote: > >> A use case that I see quite a lot of is needing to do reports and other > >> calculations on data per day/hour/etc but in the user's time zone. Th

Re: date_trunc() in a specific time zone

On 29/10/2018 17:12, Steve Crawford wrote: > On Mon, Oct 29, 2018 at 8:44 AM Vik Fearing > wrote: > > On 29/10/2018 16:26, Andreas Karlsson wrote: > > On 10/29/2018 04:18 PM, Vik Fearing wrote: > >> A use case that I see quite a lot of is needing to

Re: FETCH FIRST clause WITH TIES option

> "Tomas" == Tomas Vondra writes: >> I still think that this is the wrong approach. Implementing WITH >> TIES and PERCENT together using an implicit window function call >> kills two birds with one very small stone (the only executor change >> needed would be teaching LIMIT to be able to

Re: date_trunc() in a specific time zone

On Mon, Oct 29, 2018 at 9:40 AM Vik Fearing wrote: > I don't necessarily want to > display any dates, I could be grouping and aggregating. I can attest that this patch would be really nice functionality to have. Grouping is an often-overlooked case where timezones matter. When grouping by day (e.

Re: date_trunc() in a specific time zone

Paul A Jungwirth writes: > It would be nice to support both timestamptz and timestamp, with the > output matching whatever type you give as input. I know which to use > is controversial, but plain timestamp is the default in Ruby on Rails, > so people encounter it a lot. We already have date_trun

Re: date_trunc() in a specific time zone

On Mon, Oct 29, 2018 at 9:53 AM Tom Lane wrote: > We already have date_trunc() for timestamp-without-tz, so I'm a little > confused as to what you think is/would be missing. This patch adds a 3-arg version for date_trunc(text, timestamptz, text). I'm saying it would be nice to also have a 3-arg v

Re: date_trunc() in a specific time zone

On Mon, Oct 29, 2018 at 9:58 AM Paul A Jungwirth wrote: > This patch adds a 3-arg version for date_trunc(text, timestamptz, > text). I'm saying it would be nice to also have a 3-arg version for > date_trunc(text, timestamp, text). It would do the same thing, except > take a timestamp w/o tz and re

Re: date_trunc() in a specific time zone

On Mon, Oct 29, 2018 at 10:13 AM Paul A Jungwirth wrote: > But if those assumptions > don't hold the simple implementation of 2x AT TIME ZONE might not work > correctly. I can try it out and see Okay it looks to me that my suggestion won't work for the general case. Basically I'm looking for

replication_slots usability issue

-Hackers, Working on 9.6 today (unsure if fixed in newer versions). Had an issue where the wal was 280G despite max_wal_size being 8G. Found out there were stale replication slots from a recent base backup. I went to drop the replication slots and found that since the wal_level was set to mi

Re: replication_slots usability issue

On October 29, 2018 1:31:56 PM EDT, "Joshua D. Drake" wrote: >-Hackers, > > >Working on 9.6 today (unsure if fixed in newer versions). Had an issue >where the wal was 280G despite max_wal_size being 8G. Found out there >were stale replication slots from a recent base backup. I went to drop

Re: date_trunc() in a specific time zone

Paul A Jungwirth writes: > I guess the issue is that for w/o-tz, you need an extra parameter to > say what you're assuming you started with. Yeah, that's basically what I was wondering. I suppose we could imagine a 4-argument function to cover that case, but I do not think it's worth the trouble

Re: replication_slots usability issue

On 2018-Oct-29, Joshua D. Drake wrote: > -Hackers, > > > Working on 9.6 today (unsure if fixed in newer versions). Had an issue where > the wal was 280G despite max_wal_size being 8G. Found out there were stale > replication slots from a recent base backup. I went to drop the replication > slots

Connections hang indefinitely while taking a gin index's LWLock buffer_content lock

Hi,all In our PostgreSQL 10.2 database, two sessions of insert and autovacuum of gin index blocked in LWLock:buffer_content. This blocked checkpoint and dead tuple recycle,and we had to restart the datebase. According to the information collected from gcore, a deadlock occurred when acquiring

Re: replication_slots usability issue

On 2018-10-29 16:02:18 -0300, Alvaro Herrera wrote: > On 2018-Oct-29, Joshua D. Drake wrote: > > > -Hackers, > > > > > > Working on 9.6 today (unsure if fixed in newer versions). Had an issue where > > the wal was 280G despite max_wal_size being 8G. Found out there were stale > > replication slo

Re: Should pg 11 use a lot more memory building an spgist index?

I wrote: > Alvaro Herrera writes: >> How about modifying SysScanDescData to have a memory context member, >> which is created by systable_beginscan and destroyed by endscan? > I think it would still have problems, in that it would affect in which > context index_getnext delivers its output. We c

Re: replication_slots usability issue

On 10/29/18 11:26 AM, Andres Freund wrote: On October 29, 2018 1:31:56 PM EDT, "Joshua D. Drake" wrote: -Hackers, Working on 9.6 today (unsure if fixed in newer versions). Had an issue where the wal was 280G despite max_wal_size being 8G. Found out there were stale replication slots from a

Re: pg_promote not marked as parallel-restricted in pg_proc.dat

On Mon, Oct 29, 2018 at 10:08:45AM -0400, Tom Lane wrote: > Laurenz Albe writes: > I'd vote for PARALLEL UNSAFE myself. Otherwise you have to ask questions > about whether it's really safe to do this while parallel workers are doing > things. Perhaps the answer is "yes", but what's the point of

Re: date_trunc() in a specific time zone

> > >> A use case that I see quite a lot of is needing to do reports and other > >> calculations on data per day/hour/etc but in the user's time zone. The > >> way to do that is fairly trivial, but it's not obvious what it does so > >> reading queries becomes just a little bit more difficult. > >

Installation instructions update (pg_ctl)

Hello, The installation instructions (short version) are not consistent with the "initdb" output. The first one still uses "postgres -D", even mentions "check initdb output", but "initdb" emits "pg_ctl" commands. The attached patch updates the short install instructions and replaces "postg

Re: date_trunc() in a specific time zone

On 10/29/2018 04:36 PM, Tom Lane wrote: Andreas Karlsson writes: Hm, I am not sure if I see any major win from writing date_trunc('day', timestamptz '2001-02-16 20:38:40+00', 'Australia/Sydney') instead of date_trunc('day', timestamptz '2001-02-16 20:38:40+00' AT TIME ZONE 'Australia/Sydney')

Re: date_trunc() in a specific time zone

> "Paul" == Paul A Jungwirth writes: Paul> Thinking about this some more, perhaps the timestamp-to-timestamp Paul> version would depend on assumptions that aren't always valid. In Paul> my world the server timezone is always UTC, and the database Paul> clients always convert values to UTC

Re: Speeding up INSERTs and UPDATEs to partitioned tables

To complement the info: number of columns varies from 20 to 100 but some of the columns are composite types or arrays of composite types. The flamegraph after applying changes from patch 0002 can be seen here: https://gaiaowncloud.isdc.unige.ch/index.php/s/W3DLecAWAfkesiP shows most of the time is

Resource cleanup callbacks for foreign data wrappers

Hello All, We are using the foreign data wrapper API to integrate YugaByte DB ( https://github.com/yugabyte/yugabyte-db) with PostgreSQL, so scanning tables stored in YugaByte DB is being done through Foreign Scan callbacks. These callbacks in turn read data from the underlying YugaByte DB cluster

Re: shared-memory based stats collector

Hi, I've started looking at the patch over the past few days. I don't have any deep insights at this point, but there seems to be some sort of issue in pgstat_update_stat. When building using gcc, I do get this warning: pgstat.c: In function ‘pgstat_update_stat’: pgstat.c:648:18: warning: ‘no

Re: Speeding up INSERTs and UPDATEs to partitioned tables

On 2018/10/30 8:41, Krzysztof Nienartowicz wrote: > On Thu, Oct 25, 2018 at 5:58 PM Krzysztof Nienartowicz > wrote: >> On Tue, Oct 23, 2018 at 4:02 AM David Rowley >> wrote: >>> >>> I more meant that it might be 0002 that fixes the issue for you. I >>> just wanted to check if you'd tried 0001 and

Re: FETCH FIRST clause WITH TIES option

On 10/29/2018 05:47 PM, Andrew Gierth wrote: "Tomas" == Tomas Vondra writes: >> I still think that this is the wrong approach. Implementing WITH >> TIES and PERCENT together using an implicit window function call >> kills two birds with one very small stone (the only executor change >>

Re: partition tree inspection functions

On Mon, Oct 29, 2018 at 04:08:09PM +0900, Amit Langote wrote: > Hmm, I think we mention the word "partitioned" in the error message only > if partitioning is required to perform an operation but it's absent (for > example, trying to attach partition to a non-partitioned table) or if its > presence

pgbench doc fix

pgbench doc (and some comments in pgbench.c) regarding "-M prepared" option is not quite correct. -M querymode --protocol=querymode Protocol to use for submitting queries to the server: simple: use simple query

Re: partition tree inspection functions

On 2018/10/30 10:33, Michael Paquier wrote: > Thanks for the new version and using better index names. I have > reviewed and committed the patch, with a couple of things tweaked: > - removal of the tests on the size, they don't seem useful as part of > showing partition information. > - no need to

Re: Conflicting option checking in pg_restore

On Mon, Oct 29, 2018 at 07:11:35AM +0100, Fabien COELHO wrote: > Michaël suggests that there is no issue of external tool using the internal > function, so I'm fine with this version. > > I have switched the patch to ready for committer. One catch with this refactoring is that for example this co

Re: replication_slots usability issue

On Mon, Oct 29, 2018 at 12:13:04PM -0700, Andres Freund wrote: > I don't think this quite is the problem. ISTM the issue is rather that > StartupReplicationSlots() *needs* to check whether wal_level > minimal, > and doesn't. So you can create a slot, shutdown, change wal_level, > startup. A slot ex

Re: [Patch] pg_rewind: options to use restore_command from recovery.conf or command line

On Mon, Oct 29, 2018 at 12:09:21PM +0300, Alexey Kondratov wrote: > Currently in the patch, with dry-run option (-n) pg_rewind only fetches > missing WALs to be able to build file map, while doesn't touch any data > files. So I guess it behaves exactly as you described and we do not need a > separa

Re: INSTALL file

On Mon, Oct 29, 2018 at 01:01:47PM +0100, Andreas 'ads' Scherbaum wrote: > That is not the first file people looking at. Especially not people looking > at the GitHub copy: > > https://github.com/postgres/postgres > > I understand that there is documentation, but for the casual developer > looking

RE: [Todo item] Add entry creation timestamp column to pg_stat_replication

Thanks for your feedback and info! I registered patch in commit fest. https://commitfest.postgresql.org/20/1841/ For the record, replied on original thread. Best regards, Myungkyu, Lim -Original Message- From: Michael Paquier [mailto:mich...@paquier.xyz] Sent: Thursday, October 25, 201

Re: INSTALL file

> > > That is not the first file people looking at. Especially not people > looking > > at the GitHub copy: > > > > https://github.com/postgres/postgres > > > > I understand that there is documentation, but for the casual developer > > looking at this, it seems broken. > > FWIW, I think that people

Re: Resource cleanup callbacks for foreign data wrappers

On Tue, Oct 30, 2018 at 11:01 AM Mikhail Bautin wrote: > Hello All, > > We are using the foreign data wrapper API to integrate YugaByte DB ( > https://github.com/yugabyte/yugabyte-db) with PostgreSQL, so scanning > tables stored in YugaByte DB is being done through Foreign Scan callbacks. > These

global / super barriers (for checksums)

Hi, Magnus cornered me at pgconf.eu and asked me whether I could prototype the "barriers" I'd been talking about in the online checksumming thread. The problem there was to make sure that all processes, backends and auxiliary processes have seen the new state of checksums being enabled, and aren'

RE: [PROPOSAL]a new data type 'bytea' for ECPG

From: Michael Meskes [mailto:mes...@postgresql.org] > > bytea as a type of table definition may correspond to BLOB in the > > standard. > > Would we prefer to add a blob datatype then? > > > It seems that there is no defact and no product following to the > > standards. > > I wonder whether bytea

Re: ToDo: show size of partitioned table

Hi Mathias, Pavel, On 2018/08/17 12:26, Mathias Brossard wrote: > On Thu, Aug 16, 2018 at 12:46 AM Pavel Stehule >> >> This is question - maybe we can support older partitioning based on only >> inheritance - and the query can be more exact on PostgreSQL 10 and newer. >> >> Please, send any patch

Re: [Patch] pg_rewind: options to use restore_command from recovery.conf or command line

On Mon, Oct 22, 2018 at 02:19:07PM -0300, Alvaro Herrera wrote: > Hmm, I remember we had a project to have a new postmaster option that > would report the value of some GUC option, so instead of parsing the > file in the frontend, you'd invoke the backend to do the parsing. But I > don't know what

Re: partition tree inspection functions

On 2018/10/29 12:59, Michael Paquier wrote: > On Fri, Oct 19, 2018 at 06:55:09PM +0900, Amit Langote wrote: >> Yeah, we could make it the responsibility of the callers of >> find_all_inheritors and find_inheritance_children to check relhassubclass >> as an optimization and remove any reference to r

Re: why commutator doesn't work?

po 29. 10. 2018 v 7:35 odesílatel Fabien COELHO napsal: > > Hello Pavel, > > > I try to create operator + for varchar and integer with Oracle behave. > > > > create or replace function sum(varchar, int) > > returns int as $$ select $1::int + $2 $$ language sql; > > > > create operator + (function

pg_promote not marked as parallel-restricted in pg_proc.dat

Hi all, It looks that I forgot to mark pg_promote as parallel-restricted in 1007465 in pg_proc.dat. It seems to me that this is not a huge issue as system_views.sql redefines the function for its default values and enforces parallel-restricted, but let's be right from the start. Attached is a pa

Re: pg_promote not marked as parallel-restricted in pg_proc.dat

Michael Paquier wrote: > It looks that I forgot to mark pg_promote as parallel-restricted in > 1007465 in pg_proc.dat. It seems to me that this is not a huge issue as > system_views.sql redefines the function for its default values and > enforces parallel-restricted, but let's be right from the st

Re: [HACKERS] Restricting maximum keep segments by repslots

On Thu, Oct 25, 2018 at 9:56 PM Kyotaro HORIGUCHI wrote: > > Hello. > > At Mon, 22 Oct 2018 19:35:04 +0900, Masahiko Sawada > wrote in > > > On Thu, Sep 13, 2018 at 6:30 PM Kyotaro HORIGUCHI > > wrote: > > Sorry for the late response. The patch still can be applied to the > > It's alright. Th

Re: [Patch] pg_rewind: options to use restore_command from recovery.conf or command line

Hi Andrey, Will you add this patch to CF? I'm going to review it. Best regards, Andrey Borodin Here it is https://commitfest.postgresql.org/20/1849/ -- Alexey Kondratov Postgres Professional https://www.postgrespro.com Russian Postgres Company

Re: pg_promote not marked as parallel-restricted in pg_proc.dat

On Mon, Oct 29, 2018 at 09:41:08AM +0100, Laurenz Albe wrote: > I think that the question if pg_promote allows a parallel plan or not > is mostly academic, but the two definitions should be kept in sync. It seems to me that the presence of the trigger file written in $PGDATA means that the functio

Re: [Patch] pg_rewind: options to use restore_command from recovery.conf or command line

Something that we could think about is directly to provide a command to pg_rewind via command line. In my patch I added this option too. One can pass restore_command via -R option, e.g.: pg_rewind -P --target-pgdata=/path/to/master/pg_data --source-pgdata=/path/to/standby/pg_data -R 'cp /pat

Re: pg_promote not marked as parallel-restricted in pg_proc.dat

Michael Paquier wrote: > On Mon, Oct 29, 2018 at 09:41:08AM +0100, Laurenz Albe wrote: > > I think that the question if pg_promote allows a parallel plan or not > > is mostly academic, but the two definitions should be kept in sync. > > It seems to me that the presence of the trigger file written

Re: PostgreSQL vs SQL/XML Standards

Hi čt 25. 10. 2018 v 21:47 odesílatel Alvaro Herrera napsal: > On 2018-Oct-25, Pavel Stehule wrote: > > > I am thinking so I can fix some issues related to XMLTABLE. Please, send > me > > more examples and test cases. > > Please see Markus Winand's patch that I referenced upthread. > here is a

Re: New vacuum option to do only freezing

On Thu, Oct 4, 2018 at 6:15 PM Masahiko Sawada wrote: > > On Mon, Oct 1, 2018 at 7:20 PM Masahiko Sawada wrote: > > > > Hi, > > > > Attached patch adds a new option FREEZE_ONLY to VACUUM command. This > > option is same as FREEZE option except for it disables reclaiming dead > > tuples. That is,

Re: CVE-2017-7484-induced bugs, or, btree cmp functions are not leakproof?

Thank you for creating the patch. On 2018/10/28 20:35, Dilip Kumar wrote: > On Sat, Oct 27, 2018 at 10:07 AM Dilip Kumar wrote: >> On Fri, Oct 26, 2018 at 1:12 PM Amit Langote wrote: >>> On 2018/10/25 19:54, Dilip Kumar wrote: Basically, if the relation is RELOPT_OTHER_MEMBER_REL, we can >>>

Re: PostgreSQL vs SQL/XML Standards

> (It would not be exactly overloading, because of the special sugared >> syntax known to the parser, but it could look like overloading, and be >> intuitive to the user.) >> >> If you have convenient access to Oracle to check compatibility, could you >> compare this query? >> >> SELECT * FROM XMLT