Re: [HACKERS] MERGE SQL Statement for PG11

Ah, there is one reason not to use a mapping to CTEs to implement MERGE: it might be faster to use a single query that is a FULL OUTER JOIN of the source and target to drive the update/insert/delete operations. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make

Re: [HACKERS] MERGE SQL Statement for PG11

On Tue, Nov 07, 2017 at 03:31:22PM -0800, Peter Geoghegan wrote: > On Tue, Nov 7, 2017 at 3:29 PM, Nico Williams <n...@cryptonector.com> wrote: > > On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote: > >> Nico Williams <n...@cryptonector.com> wrote: >

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

On Mon, Nov 06, 2017 at 05:50:21PM +1300, Thomas Munro wrote: > On Fri, Oct 20, 2017 at 9:05 AM, Nico Williams <n...@cryptonector.com> wrote: > > Rebased (there were conflicts in the SGML files). > > Hi Nico > > FYI that version has some stray absolute paths in con

Re: [HACKERS] MERGE SQL Statement for PG11

On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote: > Nico Williams <n...@cryptonector.com> wrote: > >A MERGE mapped to a DML like this: I needed to spend more time reading MERGE docs from other RDBMSes. The best MERGE so far is MS SQL Server's, which looks

Re: [HACKERS] Where is it documented what role executes constraint triggers?

On Fri, Nov 03, 2017 at 02:09:00PM -0400, Chapman Flack wrote: > From a little experimenting in 9.5, it seems that a referential > integrity trigger is executed with the identity of the referencED > table's owner, but I have not been able to find this covered in > the docs. Is this a documentation

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

On Fri, Nov 03, 2017 at 01:41:45PM -0400, Peter Eisentraut wrote: > On 11/2/17 16:54, Nico Williams wrote: > > Replacing condeferred and condeferrable with a char columns also > > occurred to me, and though I assume backwards-incompatible changes to > > pg_catalog tables are

Re: [HACKERS] MERGE SQL Statement for PG11

On Thu, Nov 02, 2017 at 03:25:48PM -0700, Peter Geoghegan wrote: > Nico Williams <n...@cryptonector.com> wrote: > >A MERGE mapped to a DML like this: > > This is a bad idea. An implementation like this is not at all > maintainable. Assuming the DELETE issue can

Re: [HACKERS] MERGE SQL Statement for PG11

On Thu, Nov 02, 2017 at 02:05:19PM -0700, Peter Geoghegan wrote: > Simon Riggs wrote: > >So in your view we should make no attempt to avoid concurrent errors, > >even when we have the capability to do so (in some cases) and doing so > >would be perfectly compliant with the

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

On Thu, Nov 02, 2017 at 04:20:19PM -0400, Peter Eisentraut wrote: > I haven't really thought about this feature too hard; I just want to > give you a couple of code comments. Thanks! > I think the catalog structure, and relatedly also the parser structures, > could be made more compact. We

Re: [HACKERS] MERGE SQL Statement for PG11

On Thu, Nov 02, 2017 at 12:51:45PM -0700, Peter Geoghegan wrote: > Nico Williams <n...@cryptonector.com> wrote: > >If you want to ignore conflicts arising from concurrency you could > >always add an ON CONFLICT DO NOTHING to the INSERT DML in the mapping I > >prop

Re: [HACKERS] proposal: schema variables

On Thu, Nov 02, 2017 at 11:48:44AM -0400, Tom Lane wrote: > Nico Williams <n...@cryptonector.com> writes: > > With access controls, GUCs could become schema variables, and settings > > from postgresql.conf could move into the database itself (which I think > > wou

Re: [HACKERS] MERGE SQL Statement for PG11

On Thu, Nov 02, 2017 at 06:49:18PM +, Simon Riggs wrote: > On 1 November 2017 at 18:20, Peter Geoghegan wrote: > > In Postgres, you can avoid duplicate violations with MERGE by using a > > higher isolation level (these days, those are turned into a > > serialization error at

Re: [HACKERS] MERGE SQL Statement for PG11

If nothing else, anyone needing MERGE can port their MERGE statements to a DML with DML-containing CTEs... The generic mapping would be something like this, I think: WITH rows AS (SELECT FROM WHERE ) , updated AS ( UPDATE SET ... WHERE IN (SELECT FROM rows) /* matched */

Re: [HACKERS] proposal: schema variables

On Thu, Nov 02, 2017 at 06:05:54PM +0530, Robert Haas wrote: > On Thu, Oct 26, 2017 at 12:51 PM, Pavel Stehule > wrote: > > The variables can be modified by SQL command SET (this is taken from > > standard, and it natural) > > > > SET varname = expression; > >

Re: [HACKERS] [PATCH] Add two-arg for of current_setting(NAME, FALLBACK)

On Thu, Mar 19, 2015 at 05:41:02PM -0500, David Christensen wrote: > The two-arg form of the current_setting() function will allow a > fallback value to be returned instead of throwing an error when an > unknown GUC is provided. This would come in most useful when using > custom GUCs; e.g.:

[HACKERS] Mapping MERGE onto CTEs (Re: MERGE SQL Statement for PG11)

Is it possible to map MERGE onto a query with CTEs that does the the various DMLs, with all but the last RETURNING? Here's a sketch: WITH matched_rows AS ( SELECT FROM t WHERE ), updated_rows AS ( UPDATE t SET ... WHERE ... AND t in (SELECT j FROM

Re: [HACKERS] MERGE SQL Statement for PG11

On Mon, Oct 30, 2017 at 10:59:43AM -0700, Peter Geoghegan wrote: > On Mon, Oct 30, 2017 at 6:21 AM, Simon Riggs wrote: > > If a general purpose solution exists, please explain what it is. > > For the umpteenth time, a general purpose solution is one that more or > less

Re: [HACKERS] WIP: BRIN bloom indexes

On Fri, Oct 27, 2017 at 10:06:58PM +0200, Tomas Vondra wrote: > > + * We use an optimisation that initially we store the uint32 values > > directly, > > + * without the extra hashing step. And only later filling the bitmap space, > > + * we switch to the regular bloom filter mode. > > > > I

Re: [HACKERS] MERGE SQL Statement for PG11

On Fri, Oct 27, 2017 at 02:13:27PM -0700, srielau wrote: > While the standard may not require a unique index for the ON clause I have > never seen a MERGE statement that did not have this property. So IMHO this > is a reasonable restrictions. I don't understand how one could have a conflict upon

Re: [HACKERS] WIP: BRIN bloom indexes

On Thu, Oct 19, 2017 at 10:15:32PM +0200, Tomas Vondra wrote: A bloom filter index would, indeed, be wonderful. Comments: + * We use an optimisation that initially we store the uint32 values directly, + * without the extra hashing step. And only later filling the bitmap space, + * we switch to

Re: [HACKERS] proposal: schema variables

On Thu, Oct 26, 2017 at 09:21:24AM +0200, Pavel Stehule wrote: > Comments, notes? I like it. I would further like to move all of postgresql.conf into the database, as much as possible, as well as pg_ident.conf and pg_hba.conf. Variables like current_user have a sort of nesting context

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

Rebased (there were conflicts in the SGML files). Nico -- >From 80d284ecefa22945d507d2822f1f1a195e2af751 Mon Sep 17 00:00:00 2001 From: Nicolas Williams Date: Tue, 3 Oct 2017 00:33:09 -0500 Subject: [PATCH] Add ALWAYS DEFERRED option for CONSTRAINTs and CONSTRAINT

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

A bit more about why I want this. Suppose you have an app like PostgREST (a RESTful, Haskell-coded, HTTP front-end for PostgreSQL). PostgREST basically a proxy for PG access. Users authenticate to the proxy. The proxy authenticates to PG with its own credentials, then it does something like

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

On Wed, Oct 18, 2017 at 02:45:47PM -0700, David G. Johnston wrote: > On Wed, Oct 18, 2017 at 2:30 PM, Nico Williams <n...@cryptonector.com> > wrote: > > On Wed, Oct 18, 2017 at 02:13:29PM -0700, David G. Johnston wrote: > > > > More useful than this, for me, would be

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

On Wed, Oct 18, 2017 at 02:13:29PM -0700, David G. Johnston wrote: > > More useful than this, for me, would be a way to get the top-most user. > > That would be "session_user"? It's not quite since there's a difference between SET SESSION AUTHORIZATION and SET SESSION ROLE. But yes, it's what

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

On Wed, Oct 18, 2017 at 01:43:30PM -0700, David G. Johnston wrote: > Regardless of the merits of the proposed feature, the function > "session_user" is SQL-defined and should not be modified or enhanced. > > I could see "calling_role()" being useful - it returns the same value > as "current_role"

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

On Wed, Oct 18, 2017 at 10:15:01PM +0200, Pavel Stehule wrote: > there is a function session_user() already But it doesn't do this. Are you saying that I should add a session_user(int)? Nico -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

Re: [HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

Alternatively, a way to get at the OuterUserId? Or the outer-most current_user in the function stack? I should explain why I need this: for audit functionality where I want the triggers' procedures to be SECURITY DEFINER so only they can write to audit tables and such, but I want them to see the

[HACKERS] Interest in a SECURITY DEFINER function current_user stack access mechanism?

It'd be nice if SECURITY DEFINER functions could see what user invoked them, but current_user is the DEFINER user, naturally, since that's how this is done in fmgr_security_definer(). I was thinking that fmgr_security_definer() could keep a global pointer to a linked list (with automatic nodes)

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

FYI, I've added my patch to the commitfest. https://commitfest.postgresql.org/15/1319/ -- 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] How does postgres store the join predicate for a relation in a given query

On Tue, Oct 10, 2017 at 07:29:24PM +0530, Gourav Kumar wrote: > When you fire a query in postgresql, it will first parse the query and > create the data structures for storing various aspects of the query and > executing the query. (Like RangeTblEntry, PlannerInfo, RangeOptInfo etc.). > > I want

Re: [HACKERS] Discussion on missing optimizations

On Fri, Oct 06, 2017 at 10:19:54PM -0400, Tom Lane wrote: > Andres Freund writes: > > On 2017-10-06 21:33:16 -0400, Adam Brusselback wrote: > >> The article in question is here: > >>

Re: [HACKERS] [PATCH] A hook for session start

On Sat, Oct 07, 2017 at 05:44:00AM +0200, Pavel Stehule wrote: > 2017-10-06 21:36 GMT+02:00 Nico Williams <n...@cryptonector.com>: > > But the nice thing about them is that you need only create them once, so > > leave them in the catalog. Stats about them should not be ga

Re: [HACKERS] [PATCH] A hook for session start

On Fri, Oct 06, 2017 at 08:51:53PM +0200, Pavel Stehule wrote: > 2017-10-06 20:39 GMT+02:00 Nico Williams <n...@cryptonector.com>: > > On Fri, Oct 06, 2017 at 06:37:57PM +0200, Pavel Stehule wrote: > > > When we talked about this topic, there are two issues: > > &g

Re: [HACKERS] [PATCH] A hook for session start

On Fri, Oct 06, 2017 at 06:37:57PM +0200, Pavel Stehule wrote: > 2017-10-06 6:48 GMT+02:00 Nico Williams <n...@cryptonector.com>: > > On Fri, Oct 06, 2017 at 04:52:09AM +0200, Pavel Stehule wrote: > > > Current TEMP tables, if you do it for any session has pretty si

Re: [HACKERS] [PATCH] A hook for session start

On Fri, Oct 06, 2017 at 11:04:38AM +0800, Craig Ringer wrote: > On 6 October 2017 at 10:52, Pavel Stehule wrote: > > > It is better to work on GLOBAL TEMP tables. > > > > Current TEMP tables, if you do it for any session has pretty significant > > overhead - with

Re: [HACKERS] [PATCH] A hook for session start

On Fri, Oct 06, 2017 at 04:52:09AM +0200, Pavel Stehule wrote: > 2017-10-05 22:31 GMT+02:00 Nico Williams <n...@cryptonector.com>: > > On Tue, Aug 01, 2017 at 03:36:23PM -0400, Peter Eisentraut wrote: > > > On 7/21/17 13:14, Jim Mlodgenski wrote: > > > > When

Re: [HACKERS] fork()-safety, thread-safety

On Thu, Oct 05, 2017 at 03:34:41PM -0700, Andres Freund wrote: > On 2017-10-05 17:31:07 -0500, Nico Williams wrote: > > > >vfork() is widely demonized, but it's actually quite superior > > > >(performance-wise) to fork() when all you want to do is exec-or-ex

Re: [HACKERS] fork()-safety, thread-safety

On Thu, Oct 05, 2017 at 03:13:07PM -0700, Andres Freund wrote: > On 2017-10-05 17:02:22 -0500, Nico Williams wrote: > >A quick look at the functions called on the child side of fork() > >makes me think that it's unlikely that the children here use > >async-signa

[HACKERS] fork()-safety, thread-safety

A thread on parallelization made me wonder so I took a look: - src/bin/*/parallel.c uses threads on WIN32 - src/bin/*/parallel.c uses fork() on not-WIN32 (Ditto src/bin/pg_basebackup/pg_basebackup.c and src/backend/postmaster/syslogger.c.) A quick look at the functions called on the

Re: [HACKERS] [PATCH] A hook for session start

On Tue, Aug 01, 2017 at 03:36:23PM -0400, Peter Eisentraut wrote: > On 7/21/17 13:14, Jim Mlodgenski wrote: > > When I first saw this thread, my initial thought of a use case is to > > prepare some key application queries so they are there and ready to go. > > That would need to be before the

Re: [HACKERS] [PATCH] A hook for session start

On Fri, Jul 21, 2017 at 11:10:52PM +0800, Craig Ringer wrote: > What practical use cases are there for acting post-auth but that can't wait > until the user tries to do something? Creating TEMP schema that triggers and functions might need. Doing CREATE TEMP TABLE IF NOT EXISTS in triggers slows

Re: [HACKERS] postgres_fdw super user checks

On Thu, Sep 14, 2017 at 04:08:08PM -0400, Robert Haas wrote: > On Thu, Sep 14, 2017 at 2:33 PM, Jeff Janes wrote: > > I think that foreign tables ought to behave as views do, where they run as > > the owner rather than the invoker. No one has talked me out of it, but no > >

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

I accidentally typoed when saving a file. Here's the new patch with that typo corrected, changes to information_schema dropped, and with the addition of tab completion of ALWAYS DEFERRED in psql. Nico -- >From 97d3db0be9307eff5919821db7fc437da52ef7e3 Mon Sep 17 00:00:00 2001 From: Nicolas

Re: [HACKERS] Possible SSL improvements for a newcomer to tackle

On Wed, Oct 04, 2017 at 11:47:45AM -0700, Jeff Janes wrote: > On Mon, Oct 2, 2017 at 9:33 PM, Tom Lane wrote: > > It's possible that we could adopt some policy like "if the root.crt file > > exists then default to verify" ... but that seems messy and unreliable, > > so I'm not

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

Ah, David Fetter points out that I should also update tabe completion for psql. I'll do that at some point. I notice there's no table completion for column constraint attributes... If it's obvious enough I'll try to fix that too. -- Sent via pgsql-hackers mailing list

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

Ay, NOT WIP -- I left that in the Subject: by accident. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

[make check-world passes. Tests and docs included. Should be ready for code review.] Attached are patches to add an ALWAYS DEFERRED option to CONSTRAINTs and CONSTRAINT TRIGGERs, meaning: SET CONSTRAINTS .. IMMEDIATE will not make immediate any constraint/trigger that is declared as ALWAYS

Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

On Tue, Oct 03, 2017 at 02:51:30PM -0500, Nico Williams wrote: > Anyways, this patch is NOT passing tests at the moment, and I'm not sure > why. I'm sure I can figure it out, but first I need to understand the > failures. E.g., I see this sort of difference: > >\d testsch

Re: [HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

On Tue, Oct 03, 2017 at 10:10:59PM +0200, Andreas Joseph Krogh wrote: > +1. > > While we're in deferrable constraints land...; I even more often need > deferrable conditional unique-indexes. > In PG you now may have: > ALTER TABLE email_folder ADD CONSTRAINT some_uk UNIQUE (owner_id, >

[HACKERS] [PATCH] WIP Add ALWAYS DEFERRED option for constraints

Attached are patches to add an ALWAYS DEFERRED option to CONSTRAINTs and CONSTRAINT TRIGGERs, meaning: SET CONSTRAINTS .. IMMEDIATE will not make immediate any constraint/trigger that is declared as ALWAYS DEFERRED. I.e., the opposite of NOT DEFERRED. Perhaps I should make this NOT IMMEDIATE?

Re: [HACKERS] Possible SSL improvements for a newcomer to tackle

On Tue, Oct 03, 2017 at 11:45:24AM +0200, Adrien Nayrat wrote: > On 10/03/2017 06:15 AM, Zeus Kronion wrote: > > 2) I was surprised to learn the following from the docs: > > > >> By default, PostgreSQL will not perform any verification of the server > > certificate. This means that it is possible

Re: [HACKERS] Possible SSL improvements for a newcomer to tackle

On Tue, Oct 03, 2017 at 09:44:01AM -0400, Tom Lane wrote: > Magnus Hagander writes: > > On Tue, Oct 3, 2017 at 6:33 AM, Tom Lane wrote: > >> I'm not an SSL expert, so insert appropriate grain of salt, but AIUI the > >> question is what are you going to

Re: [HACKERS] Possible SSL improvements for a newcomer to tackle

On Tue, Oct 03, 2017 at 12:33:00AM -0400, Tom Lane wrote: > Zeus Kronion writes: > > 2) I was surprised to learn the following from the docs: > > >> By default, PostgreSQL will not perform any verification of the server > >> certificate. > > > Is there a technical reason to

Re: [HACKERS] generated columns

So yes, distinguishing stored vs. not stored computed columns is useful, especially if the expression can refer to other columns of the same row, though not only then. Examples: -- useful only if stored (assuming these never get updated) inserted_at TIMESTAMP WITHOUT TIME ZONE AS

Re: [HACKERS] generated columns

On Mon, Oct 02, 2017 at 02:30:38PM -0400, Tom Lane wrote: > Nico Williams <n...@cryptonector.com> writes: > > On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: > >> So for me, i'd rather default to compute on read, as long storing the > >> pre

Re: [HACKERS] generated columns

On Mon, Oct 02, 2017 at 12:50:14PM -0400, Adam Brusselback wrote: > I know that for my use-cases, having both options available would be very > appreciated. The vast majority of the computed columns I would use in my > database would be okay to compute on read. But there are for sure some >

Re: [HACKERS] generated columns

On Thu, Aug 31, 2017 at 12:16:43AM -0400, Peter Eisentraut wrote: > In previous discussions, it has often been a source of confusion whether > these generated columns are supposed to be computed on insert/update and > stored, or computed when read. The SQL standard is not explicit, but > appears

Re: [HACKERS] alter server for foreign table

On Sat, Sep 30, 2017 at 03:58:04PM -0400, Andrew Dunstan wrote: > On 09/30/2017 05:14 AM, Derry Hamilton wrote: > > Just to say, yes, this would be handy. I've been using a variant of > > that hack on reporting servers, while migrating systems from > > proprietary databases.  It behaves quite

Re: [HACKERS] alter server for foreign table

On Fri, Sep 29, 2017 at 10:19:03PM +0200, David Fetter wrote: > On Fri, Sep 29, 2017 at 01:47:59PM -0400, Tom Lane wrote: > > Konstantin Knizhnik writes: > > > According to Postgresql documentation it is not possible to alter server > > > for foreign table: > > >

Re: [HACKERS] Multicolumn hash indexes

On Fri, Sep 29, 2017 at 10:54:55AM -0400, Tom Lane wrote: > There are few if any indexing techniques where the first column isn't > significantly more important than the rest --- certainly that's true > for btree, for example. I do not think it's a showstopper if that's > true for hash as well.

[HACKERS] Re: COMMIT TRIGGERs, take n+1

The attached file demonstrates how to create COMMIT, BEGIN, and even session CONNECT TRIGGERs for PostgreSQL using PlPgSQL only, via normal and CONSTRAINT TRIGGERs. There have been many threads on the PG mailing lists about commit triggers, with much skepticism shown about the possible semantics

Re: [HACKERS] Multicolumn hash indexes

On Wed, Sep 27, 2017 at 11:57:23AM -0400, Tom Lane wrote: > Robert Haas writes: > > On Wed, Sep 27, 2017 at 9:56 AM, Jesper Pedersen > > wrote: > >> Maybe an initial proof-of-concept could store the hash of the first column > >> (col1) plus the

Re: [HACKERS] Multicolumn hash indexes

On Wed, Sep 27, 2017 at 09:56:26AM -0400, Jesper Pedersen wrote: > On 09/26/2017 08:11 PM, Robert Haas wrote: > >On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: > >>Tomasz Ostrowski writes: > >>>I've noticed that hash indexes can't currently (in PG10)

Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

I guess this got lost over the weekend and subsequent week (I was on vacation). On Fri, Sep 15, 2017 at 04:03:35PM -0500, Nico Williams wrote: > On Fri, Sep 15, 2017 at 04:07:33PM -0400, Tom Lane wrote: > > Nico Williams <n...@cryptonector.com> writes: > > > On Fri, Se

Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

On Fri, Sep 15, 2017 at 04:07:33PM -0400, Tom Lane wrote: > Nico Williams <n...@cryptonector.com> writes: > > On Fri, Sep 15, 2017 at 02:19:29PM -0500, Nico Williams wrote: > >> On Fri, Sep 15, 2017 at 11:26:08AM -0700, Andres Freund wrote: > >>> I thin

Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

On Fri, Sep 15, 2017 at 12:25:08PM -0700, Andres Freund wrote: > On 2017-09-15 14:19:29 -0500, Nico Williams wrote: > > Please see my post and the linked file to see why. > > The discussions here are often going to be referred back to in years, so > external links where we

Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

On Fri, Sep 15, 2017 at 02:19:29PM -0500, Nico Williams wrote: > On Fri, Sep 15, 2017 at 11:26:08AM -0700, Andres Freund wrote: > > I think you should also explain why that's a desirable set of > > semantics. Note that DEFERRED CONSTRAINT TRIGGERs already have these semantics, e

Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

On Fri, Sep 15, 2017 at 11:26:08AM -0700, Andres Freund wrote: > On 2017-09-14 14:41:12 -0500, Nico Williams wrote: > > I've read through several old threads on COMMIT TRIGGERs. Rather than > > write a lengthy post addressing past debates, here's an implementation > &

Re: [HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

On Fri, Sep 15, 2017 at 4:11 AM Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > On Thu, Sep 14, 2017 at 10:41 PM, Nico Williams <n...@cryptonector.com> > wrote: > >> https://github.com/twosigma/postgresql-contrib/ >> >> https://github.com/

[HACKERS] COMMIT TRIGGERs, take n, implemented with CONSTRAINT TRIGGERS

I've read through several old threads on COMMIT TRIGGERs. Rather than write a lengthy post addressing past debates, here's an implementation and demonstration of [an approximation of] COMMIT TRIGGERs with natural and _desirable_ semantics: - commit triggers run exactly once in any write

Re: [HACKERS] SQL/JSON in PostgreSQL

On Thu, Mar 09, 2017 at 07:12:07PM +0100, Sven R. Kunze wrote: > From my day-to-day work I can tell, the date(time) type is the only missing > piece of JSON to make it perfect for business applications (besides, maybe, > a "currency" type). And a binary type. And a chunked-string type (to avoid

Re: [HACKERS] SQL/JSON in PostgreSQL

On Thu, Mar 09, 2017 at 12:58:55PM -0500, Robert Haas wrote: > On Thu, Mar 9, 2017 at 12:48 PM, Sven R. Kunze wrote: > > On 08.03.2017 20:48, Peter van Hardenberg wrote: > >> > >> Small point of order: YAML is not strictly a super-set of JSON. > > > > I haven't read the whole

Re: [HACKERS] SQL/JSON in PostgreSQL

On Tue, Mar 07, 2017 at 10:43:16PM +0100, Sven R. Kunze wrote: > about the datetime issue: as far as I know, JSON does not define a > serialization format for dates and timestamps. Use strings in ISO 8601 format, with or without fractional seconds, and maybe with 5-digit years. > On the other

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 07, 2017 at 01:03:14PM -0500, Tom Lane wrote: > "David G. Johnston" writes: > > Actually ... now that you mention full join, I believe this works: > > select * from (select ...) s1 full join (select ...) s2 > on ((s1.*)=(s2.*)) where s1.* is distinct

Re: [HACKERS] Idea on how to simplify comparing two sets

On Tue, Feb 07, 2017 at 10:58:41AM -0500, Tom Lane wrote: > Joel Jacobson writes: > > Currently there is no simple way to check if two sets are equal. > > Uh ... maybe check whether SELECT set1 EXCEPT SELECT set2 > and SELECT set2 EXCEPT SELECT set1 are both empty? Even

Re: [HACKERS] Make subquery alias optional in FROM clause

On Thu, Feb 23, 2017 at 10:37:16AM +0100, Bernd Helmle wrote: > Am Mittwoch, den 22.02.2017, 22:17 -0500 schrieb Tom Lane: > > [ shrug... ]  Well, I won't resist this hard as long as it's done > > competently, which to me means "the subquery name doesn't conflict > > with > > anything else".  Not

Re: [HACKERS] Make subquery alias optional in FROM clause

On Wed, Feb 22, 2017 at 10:08:38AM -0500, Tom Lane wrote: > Bernd Helmle writes: > >> From time to time, especially during migration projects from Oracle to > > PostgreSQL, i'm faced with people questioning why the alias in the FROM > > clause for subqueries in PostgreSQL is

Re: [HACKERS] PoC: Make it possible to disallow WHERE-less UPDATE and DELETE

On Thu, Feb 02, 2017 at 12:14:10PM -0500, Tom Lane wrote: > Also, somebody who wants a check like that isn't necessarily going to want > "no WHERE clause" training wheels. So you're going to need to think about > facilities to enable or disable different checks. WHERE-less-ness should be

Re: [HACKERS] Contrib: pqasyncnotifier.c -- a shell command client for LISTEN

I should also note that this is on github at https://github.com/twosigma/postgresql-contrib Nico -- -- 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] Contrib: alternative MATERIALIZED VIEWs

On Mon, Jan 23, 2017 at 06:05:25PM -0600, Jim Nasby wrote: > On 1/23/17 5:38 PM, Nico Williams wrote: > >Attached is an alternative implementation of MATERIALIZED VIEWs. > > Interesting. I don't see this being accepted into core because it's plpgsql > and it depends on the

Re: [HACKERS] Contrib: pqasyncnotifier.c -- a shell command client for LISTEN

On Tue, Jan 24, 2017 at 12:48:49AM +0100, Marko Tiikkaja wrote: > Did you forget the attachment? I guess I must have. Attached this time. /* * Copyright (c) 2016 Two Sigma Open Source, LLC. * All Rights Reserved * * Permission to use, copy, modify, and distribute this software and its *

[HACKERS] Contrib: pqasyncnotifier.c -- a shell command client for LISTEN

psql(1) does not output notifications asynchronously, as it does not check for them when idle. This makes it difficult to script handling of NOTIFYs. Attached is pqasyncnotifier.c, a simple command that allows one to handle NOTIFYs asynchronously. Cheers, Nico -- -- Sent via pgsql-hackers

[HACKERS] Contrib: alternative MATERIALIZED VIEWs

Attached is an alternative implementation of MATERIALIZED VIEWs. The idea is to explore possible enahncements to the PostgreSQL MATERIALIZED VIEW features. Features: - All SQL-coded. - Keeps history of deltas computed at each refresh. - Allows DMLs of the materialized view, recording the

Updating MATERIALIZED VIEWs (Re: [HACKERS] delta relations in AFTER triggers)

[Looking at your patch I see that it's not quite related to MVs, so I'm changing the Subject. Apologies for the noise.] [Responding out of order.] On Fri, Jan 20, 2017 at 03:37:20PM -0600, Kevin Grittner wrote: > On Fri, Jan 20, 2017 at 2:08 PM, Nico Williams <n...@cryptonector.com&

Re: [HACKERS] delta relations in AFTER triggers

On Fri, Jan 20, 2017 at 01:37:33PM -0600, Kevin Grittner wrote: > On Thu, Jan 19, 2017 at 4:14 PM, Nico Williams <n...@cryptonector.com> wrote: > > Reviews welcome! I will review. > There is currently plenty of room for pseudo-MV implementations, > and may be for

Re: [HACKERS] delta relations in AFTER triggers

On Sat, Dec 17, 2016 at 08:15:49PM -0600, Kevin Grittner wrote: > On Sun, Dec 4, 2016 at 11:35 PM, Haribabu Kommi > wrote: > > Moved to next CF with "waiting on author" status. > > [...] I hope what I've done about delta relations will be mostly irrelevant given your

Re: [HACKERS] jsonb problematic operators

On Tue, Dec 13, 2016 at 10:26:24AM +0900, Michael Paquier wrote: > On Mon, Dec 12, 2016 at 10:22 PM, Greg Stark wrote: > > One option might be for Postgres to define duplicate operator names > > using ¿ or something else. I think ¿ is a good choice because it's a > > common

Re: [HACKERS] WIP: Faster Expression Processing and Tuple Deforming (including JIT)

On Tue, Dec 06, 2016 at 12:36:41PM -0800, Andres Freund wrote: > On 2016-12-06 15:25:44 -0500, Tom Lane wrote: > > I'm not entirely thrilled with the idea of this being a configure-time > > decision, because that forces packagers to decide for their entire > > audience whether it's okay to depend

Re: [HACKERS] WIP: Faster Expression Processing and Tuple Deforming (including JIT)

On Tue, Dec 06, 2016 at 12:27:51PM -0800, Andres Freund wrote: > On 2016-12-06 14:19:21 -0600, Nico Williams wrote: > > A bigger concern might be interface stability. IIRC the LLVM C/C++ > > interfaces are not very stable, but bitcode is. > > The C API is a lot more st

Re: [HACKERS] WIP: Faster Expression Processing and Tuple Deforming (including JIT)

On Tue, Dec 06, 2016 at 01:56:28PM -0500, Tom Lane wrote: > Andres Freund writes: > > I'm posting a quite massive series of WIP patches here, to get some > > feedback. > > I guess the $64 question that has to be addressed here is whether we're > prepared to accept LLVM as a

Re: [HACKERS] Tackling JsonPath support

On Mon, Dec 05, 2016 at 11:52:57AM -0500, Tom Lane wrote: > Another point here is that packagers such as Red Hat strenuously dislike > such source-code-level wrapping of other projects, because that means that > they have to rebuild multiple packages to fix any bugs found in the > wrapped code.

Re: [HACKERS] Tackling JsonPath support

On Mon, Dec 05, 2016 at 11:28:31AM -0500, Robert Haas wrote: > The overall need is that it needs to be possible for PostgreSQL to > throw an ERROR, and thus longjmp, without leaking resources. As long as one can interpose jump buffers, that should be possible. > Sometimes those errors happen

Re: [HACKERS] Tackling JsonPath support

On Fri, Dec 02, 2016 at 08:53:33AM -0500, Robert Haas wrote: > On Tue, Nov 29, 2016 at 11:50 AM, Christian Convey > wrote: > > I think I can satisfy (3) with a PG extension which provides a function that > > approximately implements JSONPath. My short-term plans are

Re: [HACKERS] Tackling JsonPath support

On Tue, Nov 29, 2016 at 05:18:17PM +0100, Petr Jelinek wrote: > Just to add to this, the SQL/JSON proposals I've seen so far, and what > Oracle, MSSQL and Teradata chose to implement already is basically > subset of jsonpath (some proposals/implementations also include > lax/strict prefix keyword

Re: [HACKERS] Tackling JsonPath support

On Mon, Nov 28, 2016 at 08:00:46PM -0700, David G. Johnston wrote: > IMO jq is considerably closer to XSLT than XPath - which leads me to figure > that since xml has both that JSON can benefit from jq and json-path. I'm > not inclined to dig too deep here but I'd rather take jq in the form of >

Re: [HACKERS] Tackling JsonPath support

On Mon, Nov 28, 2016 at 06:38:55PM -0800, Christian Convey wrote: > On Mon, Nov 28, 2016 at 6:26 PM, Nico Williams <n...@cryptonector.com> > wrote: > > > > Thanks for the explanation. It sounds like your original point was NOT > that json-path isn't sufficient for &qu

Re: [HACKERS] Tackling JsonPath support

On Mon, Nov 28, 2016 at 05:50:40PM -0800, Christian Convey wrote: > On Mon, Nov 28, 2016 at 11:23 AM, Nico Williams <n...@cryptonector.com> > wrote: > ... > > JSON Path is not expressive enough (last I looked) and can be mapped > > onto jq if need be anyways. > &g

Re: [HACKERS] matview incremental maintenance

On Mon, Jun 17, 2013 at 07:41:15AM -0700, Kevin Grittner wrote: > Since there seems to be interest in discussing incremental > maintenance of materialized views *now*, I'm starting this thread > to try to avoid polluting unrelated threads with the discussion.  I > don't intend to spend a lot of

Re: [HACKERS] Tackling JsonPath support

On Mon, Nov 28, 2016 at 05:56:41PM +0100, Pavel Stehule wrote: > Dne 28. 11. 2016 17:26 napsal uživatel "David Fetter" : > > There's another option we should also consider: jq > > . It's available under a > > PostgreSQL-compatible license, and has

  1   2   >