Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Mon, Dec 29, 2014 at 9:12 PM, Peter Geoghegan wrote: > On Mon, Dec 29, 2014 at 2:29 PM, Jeff Janes wrote: > > Using the vallock2 version of V1.8, using the test I previously > described, I > > get some all-null rows, which my code should never create. Also, the > index > > and table don't ag

Re: [HACKERS] What exactly is our CRC algorithm?

Hi. I'm re-attaching the two patches as produced by format-patch. I haven't listed any reviewers. It's either just Andres, or maybe a lot of people. Is anyone in a position to try out the patches on MSVC and see if they build and work sensibly, please? (Otherwise it may be better to remove those

Re: [HACKERS] What exactly is our CRC algorithm?

At 2014-12-29 18:44:18 +0530, a...@2ndquadrant.com wrote: > > > > +#ifdef __GNUC__ > > > + __asm__ ("crc32b %[data], %[crc]\n" : [crc] "+r" (crc) : [data] "rm" > > > (data)); > > > > Have you checked which version of gcc introduced named references to > > input/output parameters? OK, here we go:

Re: [HACKERS] Additional role attributes && superuser review

On Tue, Dec 30, 2014 at 6:52 AM, Stephen Frost wrote: > > >There is one issue that occurs to me, however. We're talking about > > >pg_dump, but what about pg_dumpall? In particular, I don't think the > > >DUMP privilege should provide access to pg_authid, as that would allow > > >the user to byp

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Mon, Dec 29, 2014 at 2:29 PM, Jeff Janes wrote: > Using the vallock2 version of V1.8, using the test I previously described, I > get some all-null rows, which my code should never create. Also, the index > and table don't agree, in this example I find 3 all-null rows in the table, > but only 2

Re: ctidscan as an example of custom-scan (Re: [HACKERS] [v9.5] Custom Plan API)

On 12/24/14, 7:54 PM, Kouhei Kaigai wrote: >>> On Mon, Dec 15, 2014 at 4:55 PM, Kouhei Kaigai >> wrote: I'm not certain whether we should have this functionality in contrib from the perspective of workload that can help, but its major worth is for an example of custom-scan interface

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

Jim Nasby wrote: > On 12/29/14, 10:53 AM, Kevin Grittner wrote: >> Merlin Moncure wrote: >> >>> Serialization errors only exist as a concession to concurrency >>> and performance. Again, they should be returned as sparsely as >>> possible because they provide absolutely (as Tom pointed >>> out)

Re: [HACKERS] patch : Allow toast tables to be moved to a different tablespace

Hi, Here is my review of the feature. - I have not worked enough with tablespaces to see if this patch would be useful. There was some uncertainty about this upstream. - Would it not be enough to simply allow running ALTER TABLE SET TABLESPACE on the TOAST tables? Or is manually modifying th

Re: [HACKERS] nls and server log

On 12/29/14, 7:40 PM, Craig Ringer wrote: On 12/30/2014 06:39 AM, Jim Nasby wrote: How much of this issue is caused by trying to machine-parse log files? Is a better option to improve that case, possibly doing something like including a field in each line that tells you the encoding for that

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Mon, Dec 29, 2014 at 2:29 PM, Jeff Janes wrote: > I've attached an example output of querying via index and via full table > scan, and also the pageinspect output of the blocks which have the 3 rows, > in case that is helpful. You might have also included output from pageinspect's bt_page_item

[HACKERS] Documentation of bt_page_items()'s ctid field

Attached documentation patch describes the purpose of bt_page_items()'s ctid field. This has come up enough times in disaster recovery or testing scenarios that I feel it's worth drawing particular attention to. -- Peter Geoghegan diff --git a/doc/src/sgml/pageinspect.sgml b/doc/src/sgml/pageinsp

Re: [HACKERS] Additional role attributes && superuser review

On 12/29/14, 7:22 PM, Stephen Frost wrote: One other point is that this shouldn't imply any other privileges, imv. > >I'm specifically thinking of BYPASSRLS- that's independently grantable > >and therefore should be explicitly set, if it's intended. Things > >should work 'sanely' with any combin

Re: [HACKERS] Detecting backend failures via libpq / DBD::Pg

On 12/29/14, 6:43 PM, Greg Sabino Mullane wrote: I am working on enhancing the ping() method of DBD::Pg. The goal of that is for a user to be able to determine if the connection to the database is still valid. This is actually a VERY common thing for monitoring frameworks to do. Currently, the

Re: [HACKERS] nls and server log

On 12/30/2014 06:39 AM, Jim Nasby wrote: >> > > How much of this issue is caused by trying to machine-parse log files? > Is a better option to improve that case, possibly doing something like > including a field in each line that tells you the encoding for that entry? That'd be absolutely ghastly

Re: [HACKERS] CATUPDATE confusion?

Adam, * Adam Brightwell (adam.brightw...@crunchydatasolutions.com) wrote: > Given this discussion, I have attached a patch that removes CATUPDATE for > review/discussion. Thanks! > One of the interesting behaviors (or perhaps not) is how 'pg_class_aclmask' > handles an invalid role id when check

Re: [HACKERS] Additional role attributes && superuser review

Jim, * Jim Nasby (jim.na...@bluetreble.com) wrote: > On 12/29/14, 4:01 PM, Stephen Frost wrote: > >That said, a 'DUMP' privilege which allows the user to dump the contents > >of the entire database is entirely reasonable. We need to be clear in > >the documentation though- such a 'DUMP' privilege

Re: [HACKERS] Detecting backend failures via libpq / DBD::Pg

> "Greg" == Greg Sabino Mullane writes: Greg> I am working on enhancing the ping() method of DBD::Pg. The Greg> goal of that is for a user to be able to determine if the Greg> connection to the database is still valid. The basic way we do Greg> this is to send a simple SELECT via PQexec

Re: [HACKERS] Additional role attributes && superuser review

On 12/29/14, 4:01 PM, Stephen Frost wrote: Adam, * Adam Brightwell (adam.brightw...@crunchydatasolutions.com) wrote: I'd suggest it's called DUMP if that's what it allows, to keep it separate from the backup parts. Makes sense to me. I'm fine calling it 'DUMP', but for different reasons. W

[HACKERS] Detecting backend failures via libpq / DBD::Pg

-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I am working on enhancing the ping() method of DBD::Pg. The goal of that is for a user to be able to determine if the connection to the database is still valid. The basic way we do this is to send a simple SELECT via PQexec and then check for

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

On 12/29/14, 10:53 AM, Kevin Grittner wrote: Merlin Moncure wrote: Serialization errors only exist as a concession to concurrency and performance. Again, they should be returned as sparsely as possible because they provide absolutely (as Tom pointed out) zero detail to the application. That

Re: [HACKERS] CATUPDATE confusion?

All, On Sat, Dec 27, 2014 at 6:31 PM, Noah Misch wrote: > On Sat, Dec 27, 2014 at 06:26:02PM -0500, Tom Lane wrote: > > Stephen Frost writes: > > > * Tom Lane (t...@sss.pgh.pa.us) wrote: > > >> Plan C (remove CATUPDATE altogether) also has some merit. But adding > a > > >> superuser override t

Re: [HACKERS] Misaligned BufferDescriptors causing major performance problems on AMD

On 2014-12-29 16:59:05 -0500, Bruce Momjian wrote: > I am glad someone else considers this important. I do consider it important. I just considered the lwlock scalability more important... > Andres reported the above 2x pgbench difference in February, but no > action was taken as everyone felt th

Re: [HACKERS] INSERT ... ON CONFLICT {UPDATE | IGNORE}

Hi Jeff, On Mon, Dec 29, 2014 at 2:29 PM, Jeff Janes wrote: > Using the vallock2 version of V1.8, using the test I previously described, I > get some all-null rows, which my code should never create. Also, the index > and table don't agree, in this example I find 3 all-null rows in the table, >

Re: [HACKERS] 9.5: Better memory accounting, towards memory-bounded HashAgg

On 12/28/14, 2:45 PM, Peter Geoghegan wrote: On Sun, Dec 28, 2014 at 12:37 PM, Jeff Davis wrote: Do others have similar numbers? I'm quite surprised at how little work_mem seems to matter for these plans (HashJoin might be a different story though). I feel like I made a mistake -- can someone p

Re: [HACKERS] nls and server log

On 12/28/14, 2:56 AM, Craig Ringer wrote: On 12/25/2014 02:35 AM, Euler Taveira wrote: Hi, Currently the same message goes to server log and client app. Sometimes it bothers me since I have to analyze server logs and discovered that lc_messages is set to pt_BR and to worse things that stup^H^H^

Re: [HACKERS] replicating DROP commands across servers

Here's a patch that tweaks the grammar to use TypeName in COMMENT, SECURITY LABEL, and DROP for the type and domain cases. The required changes in the code are pretty minimal, thankfully. Note the slight changes to the new object_address test also. I think I'm pretty much done with this now, so

Re: [HACKERS] Additional role attributes && superuser review

Adam, * Adam Brightwell (adam.brightw...@crunchydatasolutions.com) wrote: > > I'd suggest it's called DUMP if that's what it allows, to keep it separate > > from the backup parts. > > Makes sense to me. I'm fine calling it 'DUMP', but for different reasons. We have no (verifiable) idea what cli

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

[errata] Kevin Grittner wrote: > Quoting from the peer-reviewed paper presented in Istanbul[1]: That should have been [3], not [1]. -- 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] Misaligned BufferDescriptors causing major performance problems on AMD

On Sat, Dec 27, 2014 at 08:05:42PM -0500, Robert Haas wrote: > On Wed, Dec 24, 2014 at 11:20 AM, Andres Freund > wrote: > > I just verified that I can still reproduce the problem: > > > > # aligned case (max_connections=401) > > afreund@axle:~$ pgbench -P 1 -h /tmp/ -p5440 postgres -n -M prepared

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

Merlin Moncure wrote: > On Mon, Dec 29, 2014 at 10:53 AM, Kevin Grittner wrote: >> The semantics are so imprecise that Tom argued that we should >> document that transactions should be retried from the start when >> you get the duplicate key error, since it *might* have been caused >> by a race c

Re: [HACKERS] Additional role attributes && superuser review

Magnus, Thanks for the feedback. >> BACKUP - allows role to perform pg_dump* backups of whole database. >> > > I'd suggest it's called DUMP if that's what it allows, to keep it separate > from the backup parts. > Makes sense to me. That seems really bad names, IMHO. Why? Because we use WAL and

Re: [HACKERS] [GENERAL] ON_ERROR_ROLLBACK

On 12/29/2014 09:55 AM, Tom Lane wrote: Adrian Klaver writes: So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you can only turn it off with 'off'. With ON_ERROR_STOP 1/on and 0/off both seem to work. Is this expected? Given the lack of previous complaints, thi

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

On Mon, Dec 29, 2014 at 10:47 AM, Nikita Volkov wrote: >> [combining replies -- nikita, better not to top-post (FYI)] [combining replied again] > I'm sorry. I don't know what you mean. I just replied to an email. http://www.idallen.com/topposting.html >> To prove your statement, you need to de

Re: [HACKERS] recovery_min_apply_delay with a negative value

On Sun, Dec 28, 2014 at 12:31 PM, Michael Paquier wrote: > > Hi all, > > While reviewing another patch, I have noticed that recovery_min_apply_delay can have a negative value. And the funny part is that we actually attempt to apply a delay even in this case, per se this condition recoveryApplyDela

Re: [HACKERS] [GENERAL] ON_ERROR_ROLLBACK

Adrian Klaver writes: > So it seems you can turn ON_ERROR_ROLLBACK on with either 1 or 'on', but you > can only turn it off with 'off'. > With ON_ERROR_STOP 1/on and 0/off both seem to work. > Is this expected? on_error_stop_hook() uses ParseVariableBool, while on_error_rollback_hook() uses so

Re: [HACKERS] replicating DROP commands across servers

(The changes in the regression test are bogus, BTW; I didn't care enough to get them fixed before sorting out the rest of the mess.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services commit 89c8cbed0072ad4d921128b834

Re: [HACKERS] replicating DROP commands across servers

Alvaro Herrera wrote: > Patch 0005 adds getObjectIdentityParts(), which returns the object > identity in arrays that can be passed to pg_get_object_address. This > part needs slight revisions so I'm not sure I will be able to push > tomorrow. Here's a fresh version of this patch. I chose to add

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

I believe, the objections expressed in this thread miss a very important point of all this: the isolation property (the "I" in ACID) is violated. Here’s a quote from the Wikipedia article on ACID : The isolation property ensures that the concurrent execution of

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

> [combining replies -- nikita, better not to top-post (FYI)] I'm sorry. I don't know what you mean. I just replied to an email. > To prove your statement, you need to demonstrate how a transaction left the database in a bad state given concurrent activity without counting failures. 1. Transacti

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

Merlin Moncure wrote: > Serialization errors only exist as a concession to concurrency > and performance. Again, they should be returned as sparsely as > possible because they provide absolutely (as Tom pointed > out) zero detail to the application. That is false. They provide an *extremely* v

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

On Mon, Dec 29, 2014 at 9:44 AM, Greg Stark wrote: > On Mon, Dec 29, 2014 at 3:31 PM, Merlin Moncure wrote: >> In that case: we don't agree. How come duplicate key errors would be >> reported as serialization failures but not RI errors (for example, >> inserting a record pointing to another reco

Re: [HACKERS] Publish autovacuum informations

2014-12-29 17:03 GMT+01:00 Tom Lane : > Guillaume Lelarge writes: > > All in all, I want to get informations that are typically stored in > shared > > memory, handled by the autovacuum launcher and autovacuum workers. I > first > > thought I could get that by writing some C functions embedded in

Re: [HACKERS] Publish autovacuum informations

Guillaume Lelarge writes: > All in all, I want to get informations that are typically stored in shared > memory, handled by the autovacuum launcher and autovacuum workers. I first > thought I could get that by writing some C functions embedded in an > extension. But it doesn't seem to me I can acc

[HACKERS] Publish autovacuum informations

Hey, There are times where I would need more informations on the autovacuum processes. I'd love to know what each worker is currently doing. I can get something like this from the pg_stat_activity view but it doesn't give me as much informations as the WorkerInfoData struct. I'd also love to hav

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

On Mon, Dec 29, 2014 at 3:31 PM, Merlin Moncure wrote: > In that case: we don't agree. How come duplicate key errors would be > reported as serialization failures but not RI errors (for example, > inserting a record pointing to another record which a concurrent > transaction deleted)? The key qu

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

On Mon, Dec 29, 2014 at 9:09 AM, Kevin Grittner wrote: > Merlin Moncure wrote: >> In other words, the current behavior is: >> txn A,B begin >> txn A inserts >> txn B inserts over A, locks, waits >> txn A commits. B aborts with duplicate key error > > What I'm proposing is that for serializable t

Re: [HACKERS] Serialization exception : Who else was involved?

Craig Ringer wrote: > I don't see how that'd necessarily correctly identify the > query/queries in the other tx that're involved, though. > > Perhaps I'm thinking in terms of more complicated serialization > failures? Yeah, it might be possible to provide useful information about specific confli

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

Merlin Moncure wrote: > Well, I'm arguing that duplicate key errors are not serialization > failures unless it's likely the insertion would succeed upon a retry; > a proper insert, not an upsert. If that's the case with what you're > proposing, then it makes sense to me. But that's not what it

Re: [HACKERS] PATCH: decreasing memory needlessly consumed by array_agg

2014-12-29 14:38 GMT+07:00 Jeff Davis : > Just jumping into this patch now. Do we think this is worth changing the > signature of functions in array.h, which might be used from a lot of > third-party code? We might want to provide new functions to avoid a > breaking change. > V6 patch from Tomas

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

On Mon, Dec 29, 2014 at 8:03 AM, Kevin Grittner wrote: > Merlin Moncure wrote: >> On Fri, Dec 26, 2014 at 12:38 PM, Kevin Grittner >> wrote: >>> Tom Lane wrote: >>> Just for starters, a 40XXX error report will fail to provide the duplicated key's value. This will be a functional regr

Re: [HACKERS] BUG #12330: ACID is broken for unique constraints

Merlin Moncure wrote: > On Fri, Dec 26, 2014 at 12:38 PM, Kevin Grittner wrote: >> Tom Lane wrote: >> >>> Just for starters, a 40XXX error report will fail to provide the >>> duplicated key's value.  This will be a functional regression, >> >> Not if, as is normally the case, the transaction is

Re: [HACKERS] What exactly is our CRC algorithm?

At 2014-12-29 13:22:28 +0100, and...@2ndquadrant.com wrote: > > How about pg_choose_crc_impl() or something? Done. > _sb8? Unless I miss something it's not slice by 8 but rather bytewise? This is meant to apply on top of the earlier patches I posted to implement slice-by-8. I'll attach both here

Re: [HACKERS] What exactly is our CRC algorithm?

Hi, On 2014-12-25 11:57:29 +0530, Abhijit Menon-Sen wrote: > -extern pg_crc32 pg_comp_crc32c(pg_crc32 crc, const void *data, size_t len); > +extern void pg_init_comp_crc32c(void); How about pg_choose_crc_impl() or something? > +extern pg_crc32 (*pg_comp_crc32c)(pg_crc32 crc, const void *data, si

Re: [HACKERS] [COMMITTERS] pgsql: Keep track of transaction commit timestamps

On 2014-12-29 12:50:23 +0200, Heikki Linnakangas wrote: > On 12/29/2014 12:39 PM, Petr Jelinek wrote: > >On 29/12/14 11:16, Andres Freund wrote: > >>On 2014-12-29 12:06:07 +0200, Heikki Linnakangas wrote: > >>>To be honest, I think this patch should be reverted. Instead, we should > >>>design a sys

Re: [HACKERS] pgaudit - an auditing extension for PostgreSQL

Hi. I've changed pgaudit to work as you suggested. A quick note on the implementation: pgaudit was already installing an ExecutorCheckPerms_hook anyway; I adapted code from ExecRTECheckPerms to check if the audit role has been granted any of the permissions required for the operation. This means

Re: [HACKERS] The return value of allocate_recordbuf()

On 12/26/2014 09:31 AM, Fujii Masao wrote: Hi, While reviewing FPW compression patch, I found that allocate_recordbuf() always returns TRUE though its source code comment says that FALSE is returned if out of memory. Its return value is checked in two places, but which is clearly useless. alloc

Re: [HACKERS] [COMMITTERS] pgsql: Keep track of transaction commit timestamps

On 12/29/2014 12:39 PM, Petr Jelinek wrote: On 29/12/14 11:16, Andres Freund wrote: On 2014-12-29 12:06:07 +0200, Heikki Linnakangas wrote: To be honest, I think this patch should be reverted. Instead, we should design a system where extensions can define their own SLRUs to store additional per

Re: [HACKERS] [COMMITTERS] pgsql: Keep track of transaction commit timestamps

On 29/12/14 11:16, Andres Freund wrote: On 2014-12-29 12:06:07 +0200, Heikki Linnakangas wrote: That's a little bit better, but I have to say I'm still not impressed. There are so many implicit assumptions in the system. The first assumption is that a 32-bit node id is sufficient. Seriously? A

Re: [HACKERS] [COMMITTERS] pgsql: Keep track of transaction commit timestamps

On 2014-12-29 12:06:07 +0200, Heikki Linnakangas wrote: > That's a little bit better, but I have to say I'm still not impressed. There > are so many implicit assumptions in the system. The first assumption is that > a 32-bit node id is sufficient. Seriously? Are we going to build facilities for re

Re: [HACKERS] [COMMITTERS] pgsql: Keep track of transaction commit timestamps

On 12/19/2014 11:30 AM, Petr Jelinek wrote: as promised I am sending code-comment patch that explains the use of commit timestamps + nodeid C api for the conflict resolution, comments welcome. That's a little bit better, but I have to say I'm still not impressed. There are so many implicit ass