Re: [HACKERS] Parallel bitmap heap scan

[Dilip Kumar]

On Fri, Jan 6, 2017 at 10:47 AM, Amit Khandekar  wrote:
> This looks good now.

Thanks..
>
>
> Further points below 

Thanks for the review.


> = nodeBItmapHeapscan.c ==
>
>
> In BitmapHeapNext(), the following code is relevant only for tbm
> returned from MultiExecProcNode().
> if (!tbm || !IsA(tbm, TIDBitmap))
> elog(ERROR, "unrecognized result from subplan");

Fixed

> --
>
> BitmapHeapScanState->is_leader field looks unnecessary. Instead, a
> local variable is_leader in BitmapHeapNext() will solve the purpose.
> This is because is_leader is used only in BitmapHeapNext().

Fixed
>
> --
>
> In BitmapHeapNext(), just before tbm_restore_shared_members() is
> called, we create tbm using tbm_create(). I think tbm_create() does
> not make sense for shared tbm. Whatever fields are required, will be
> restored in tbm_restore_shared_members(). The other fields which do
> not make sense in a restored tbm are not required to be initialized
> using tbm_create(). So I think tbm_restore_shared_members() itself can
> call makeNode(TIDBitmap). (Also it is not required to initialize
> tbm->allocator; see note below in tidbitmap.c section). So
> tbm_restore_shared_members() itself can call tbm_set_parallel().
> Looking at all this, it looks better to have the function name changed
> to tbm_attach(parallel_tbm) or tbm_restore(parallel_tbm) rather than
> tbm_restore_shared_members(). The function header anyways (rightly)
> says  : Attach worker to shared TID bitmap.

Fixed

>
> -
>
> From what I understand, the leader worker does not have to create its
> iterators before waking up the other workers, as long as it makes sure
> it copies tbm fields into shared memory before waking workers. But in
> the patch, tbm_begin_iterate() is called *before* the
> ConditionVariableBroadcast() is called. So I feel, we can shift the
> code inside the "if (node->is_leader)" to a place inside the "if
> (pbminfo == NULL || pbms_is_leader(pbminfo))" condition block, just
> after MultiExecProcNode() call. (And we won't even need is_leader
> local variable as well). This way now the other workers will start
> working sooner.

Correct, Fixed.

>
>
>
> == tidbitmap.c ===
>
>
> The new #include's are not in alphabetical order.

Done..
>
> --
>
> ParallelTIDBitmap.inited is unused, and I believe, not required.

Fixed
>
> --
>
> For leader worker, the new TIDBitmap fields added for parallel bitmap
> *are* valid while the tid is being built. So the below comment should
> be shifted accordingly :
> /* these are valid when iterating is true: */
> Better still, the shared tbm-related fields can be kept in the end,
> and a comment should be added that these are for shared tbm.
>

Done
> --
>
> It seems, the comment below the last ParallelTIDBitmap field is not relevant :
> /* table to dsa_pointer's array. */

Fixed..
>
> --
>
> tbm->allocator field does not seem to be required. A new allocator can
> be just palloc'ed in tbm_create_pagetable(), and passed to
> pagetable_create(). SH_CREATE() stores this allocator in the
> SH_TYPE->alloc field, and fetches the same whenever it needs it for
> calling any of the allocator functions. So we can remove the
> tbm->allocator field and shift "palloc(sizeof(pagetable_alloc))" call
> from tbm_create() to tbm_create_pagetable().

Done
>
> --
>
> In tbm_free() :
> I think we should call pagetable_destroy() even when tbm is shared, so
> that the hash implementation gets a chance to free the hash table
> structure. I understand that the hash table structure itself is not
> big, so it will only be a small memory leak, but it's better to not
> assume that. Instead, let SH_DESTROY() call HashFree(). Then, in
> tbm_free_shared(), we can skip the dsa_free() call if tbm->iterating
> is false. Basically, tbm bitmap implementation should deduce from the
> bitmap state whether it should free the shared data, rather than
> preventing a call to SH_DESTROY().

Fixed
>
> ---
>
> In tbm_begin_iterate(), for shared tbm, internal structures from
> simplehash.h are assumed to be known. For e.g., the hash entries will
> always be present in one single array, and also the entry status is
> evaluated using pagetable_IN_USE. Is simplehash.h designed keeping in
> mind that these things are suppose to be exposed ?
>
> I understand that the hash table handle is local to the leader worker,
> and so it is not accessible to other workers. And so, we cannot use
> pagetable_iterate() to scan the hash table. So, how about copying the
> SH_TYPE structure and making it accessible to other workers ? If we
> have something like SH_ATTACH() or SH_COPY(), this will copy the
> relevant fields that are sufficient to restore the SH_TYPE structure,
> and other workers can start using this hash table after assigning dsa
> array back to tb->data. Something like HASH_ATTACH used in dynahash.c.

This looks cleaner, 

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

[Jim Nasby]

On 1/5/17 12:04 AM, David Fetter wrote:

+errmsg("UPDATE requires a WHERE clause when 
require_where.delete is set to on"),


ISTM that message is no longer true.

The second if could also be an else if too.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] pg_hba_file_settings view patch

[Haribabu Kommi]

On Tue, Nov 29, 2016 at 9:15 PM, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:

> Here's backtrace and some debugging information
> Program terminated with signal 11, Segmentation fault.
> #0  0x007f96cd in shm_mq_sendv (mqh=0x121e998,
> iov=0x7ffc9b7b79f0, iovcnt=2, nowait=1 '\001') at shm_mq.c:357
> 357Assert(mq->mq_sender == MyProc);
> (gdb) where
> #0  0x007f96cd in shm_mq_sendv (mqh=0x121e998,
> iov=0x7ffc9b7b79f0, iovcnt=2, nowait=1 '\001') at shm_mq.c:357
> #1  0x006d8387 in mq_putmessage (msgtype=88 'X', s=0x0, len=0)
> at pqmq.c:165
> #2  0x00515147 in ParallelWorkerMain (main_arg=141900502) at
> parallel.c:1120
> #3  0x00783063 in StartBackgroundWorker () at bgworker.c:726
> #4  0x00795b77 in do_start_bgworker (rw=0x1216f00) at
> postmaster.c:5535
> #5  0x00795e4f in maybe_start_bgworker () at postmaster.c:5710
> #6  0x00794eb3 in sigusr1_handler (postgres_signal_arg=10) at
> postmaster.c:4959
> #7  
> #8  0x2b005933a693 in select () from /lib/x86_64-linux-gnu/libc.so.6
> #9  0x00790720 in ServerLoop () at postmaster.c:1665
> #10 0x0078fe76 in PostmasterMain (argc=8, argv=0x11eef40) at
> postmaster.c:1309
> #11 0x006d8f1d in main (argc=8, argv=0x11eef40) at main.c:228
> (gdb) p mq->mq_sender
> Cannot access memory at address 0x6b636568635f707d
> (gdb) p mq
> $1 = (shm_mq *) 0x6b636568635f706d
>

I found the reason to the crash. This is because of new discard_hba() call
that
is added in InitPostgres after authentication.

The PostmasterContext is deleted and set it to NULL for all children
processes
except normal backend process. But because of addition of discard_hba()
function
call in InitPostgres, the parsed_hba_context is checked for NULL and freed.
For
all other childrens except normal backend, this pointer is not NULL and it
leads to
freeing of some other memory and that leads to the crash of the parallel
worker.

The freeing of parsed_hba_context memory is required only for normal backend
processes after authentication, so moved the discard_hba() function call
into the
if block solved the problem.

But anyway the logic of reading hba rules is changed for pg_hba_rules view,
so
this patch is not required anyway. Just for reference I attached updated
patch.


Regards,
Hari Babu
Fujitsu Australia


discard_hba_and_ident_cxt_2.patch
Description: Binary data

-- 
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] createdb warnings on OS X

[Jim Nasby]

On 1/8/17 6:03 PM, Tom Lane wrote:

decibel@decina:[17:04]~/pgsql/HEAD (temp *$%)$uname -a
Darwin decina.local 15.6.0 Darwin Kernel Version 15.6.0: Wed Nov  2 20:30:56 
PDT 2016; root:xnu-3248.60.11.1.2~2/RELEASE_X86_64 x86_64

I can never remember how Darwin kernel versions map to OS X versions?
But the real question is whether you've got (any vestiges of) a manual
readline installation.


OS X 10.11.6

From config.log...
configure:13546: ccache clang -Qunused-arguments -fcolor-diagnostics -o 
conftest -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv 
-Wno-unused-command-line-argument -O2  -I/opt/local/include/libxml2 
-L/opt/local/lib  conftest.c -lxml2 -lz -lreadline -lm  >&5


I recently discovered things now work even if I don't supply 
--with-includes=/opt/local/include and --with-libraries=/opt/local/lib 
to configure, so maybe that's it.


In any case it doesn't bother me enough to investigate it right now. I 
just wanted to make sure it wasn't something more serious.


Thanks!
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Increase pltcl test coverage

[Jim Nasby]

On 1/8/17 11:25 AM, Tom Lane wrote:

But I don't understand
how you got the sample output shown in the patch.  Is this based
on some unsubmitted changes in pltcl's error handling?


AFAICT you've got everything. What I had on my end is:

create function public.tcl_error_handling_test(text)
 returns text
 language pltcl
as $function$
if {[catch $1 err]} {
# Set keys that will change over time to fixed values
array set myArray $::errorCode
set myArray(funcname) "'funcname'"
set myArray(lineno) 'lineno'
set myArray(POSTGRES) 'POSTGRES'

# Format into something nicer
set vals []
foreach {key} [lsort [array names myArray]] {
set value [string map {"\n" "\n\t"} $myArray($key)]
lappend vals "$key: $value"
}
return [join $vals "\n"]
} else {
return "no error"
}
$function$
;

Maybe it's a version difference?

echo 'puts [info patchlevel];exit 0' | tclsh
8.6.6

Anyway, attached is a complete new patch that fixes that issue (and a 
couple test diffs I missed :/), as well as the utf_e2u issue you 
discovered. I've applied this patch to master via git apply and run it 
through make check-world, so hopefully this puts the horse out to pasture.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)
diff --git a/src/pl/tcl/expected/pltcl_queries.out 
b/src/pl/tcl/expected/pltcl_queries.out
index 3a9fef3447..564ec8a294 100644
--- a/src/pl/tcl/expected/pltcl_queries.out
+++ b/src/pl/tcl/expected/pltcl_queries.out
@@ -1,3 +1,7 @@
+BEGIN;
+SET LOCAL client_min_messages = WARNING;
+CREATE EXTENSION IF NOT EXISTS plpgsql;
+COMMIT;
 -- suppress CONTEXT so that function OIDs aren't in output
 \set VERBOSITY terse
 insert into T_pkey1 values (1, 'key1-1', 'test key');
@@ -185,12 +189,23 @@ select * from T_pkey2 order by key1 using @<, key2 
collate "C";
 
 -- show dump of trigger data
 insert into trigger_test values(1,'insert');
-NOTICE:  NEW: {i: 1, v: insert}
+NOTICE:  NEW: {}
+NOTICE:  OLD: {}
+NOTICE:  TG_level: STATEMENT
+NOTICE:  TG_name: statement_trigger
+NOTICE:  TG_op: INSERT
+NOTICE:  TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: BEFORE
+NOTICE:  args: {42 {statement trigger}}
+NOTICE:  NEW: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: 
insert}
 NOTICE:  OLD: {}
 NOTICE:  TG_level: ROW
 NOTICE:  TG_name: show_trigger_data_trig
 NOTICE:  TG_op: INSERT
-NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
 NOTICE:  TG_relid: bogus:12345
 NOTICE:  TG_table_name: trigger_test
 NOTICE:  TG_table_schema: public
@@ -232,13 +247,37 @@ NOTICE:  TG_table_name: trigger_test_view
 NOTICE:  TG_table_schema: public
 NOTICE:  TG_when: {INSTEAD OF}
 NOTICE:  args: {24 {skidoo view}}
+update trigger_test set v = 'update', test_skip=true where i = 1;
+NOTICE:  NEW: {}
+NOTICE:  OLD: {}
+NOTICE:  TG_level: STATEMENT
+NOTICE:  TG_name: statement_trigger
+NOTICE:  TG_op: UPDATE
+NOTICE:  TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: BEFORE
+NOTICE:  args: {42 {statement trigger}}
+NOTICE:  SKIPPING OPERATION UPDATE
 update trigger_test set v = 'update' where i = 1;
-NOTICE:  NEW: {i: 1, v: update}
-NOTICE:  OLD: {i: 1, v: insert}
+NOTICE:  NEW: {}
+NOTICE:  OLD: {}
+NOTICE:  TG_level: STATEMENT
+NOTICE:  TG_name: statement_trigger
+NOTICE:  TG_op: UPDATE
+NOTICE:  TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
+NOTICE:  TG_relid: bogus:12345
+NOTICE:  TG_table_name: trigger_test
+NOTICE:  TG_table_schema: public
+NOTICE:  TG_when: BEFORE
+NOTICE:  args: {42 {statement trigger}}
+NOTICE:  NEW: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: 
update}
+NOTICE:  OLD: {i: 1, test_argisnull: f, test_return_null: f, test_skip: f, v: 
insert}
 NOTICE:  TG_level: ROW
 NOTICE:  TG_name: show_trigger_data_trig
 NOTICE:  TG_op: UPDATE
-NOTICE:  TG_relatts: {{} i v}
+NOTICE:  TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
 NOTICE:  TG_relid: bogus:12345
 NOTICE:  TG_table_name: trigger_test
 NOTICE:  TG_table_schema: public
@@ -246,16 +285,39 @@ NOTICE:  TG_when: BEFORE
 NOTICE:  args: {23 skidoo}
 delete from trigger_test;
 NOTICE:  NEW: {}
-NOTICE:  OLD: {i: 1, v: update}
+NOTICE:  OLD: {}
+NOTICE:  TG_level: STATEMENT
+NOTICE:  TG_name: statement_trigger
+NOTICE:  TG_op: DELETE
+NOTICE:  TG_relatts: {{} i v {} test_skip test_return_null test_argisnull}
+NOTICE:  TG_relid: 

Re: [HACKERS] RustgreSQL

[Jim Nasby]

On 1/8/17 5:56 PM, Greg Stark wrote:

On 8 January 2017 at 21:50, Jim Nasby  wrote:

Somewhat related to that... it would be useful if Postgres had "fenced"
functions; functions that ran in a separate process and only talked to a
backend via a well defined API (such as libpq). There's two major advantages
that would give us:


The problem with this is that any of the "interesting" extensions need
to use the server API. That is, they need to be able to do things like
throw errors, expand toast data, etc.


There's plenty of interesting things you can do in python or R, even 
without that ability.



IMHO just about anything you could do in an external process would be
something you could much more easily and conveniently do in the
client. And it would be more flexible and scalable as well as it's a
lot easier to add more clients than it is to scale up the database.


Well, then you're suffering from serious network latency, and you're 
forced into worrying about endian issues and what-not. Those problems 
don't exist when you're running on the same server. There's also things 
that might make sense on a local-only protocol but would make no sense 
with an external one. My guess is that you'd ultimately want a protocol 
that's something "in between" SPI and libpq.



That said, there were several pl language implementations that worked
this way. IIRC one of the Java pl languages ran in a separate Java
process.

I think the solution to the problem you're describing is the project
formerly known as NaCl
https://en.wikipedia.org/wiki/Google_Native_Client


Possibly; depends on if it would allow running things like R or python.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] merging some features from plpgsql2 project

[Jim Nasby]

On 1/8/17 12:03 AM, Pavel Stehule wrote:

BTW, I do wish you could change the label of the scope that
arguments went into, so that you could use that label to refer
to function parameters. If we allowed that it'd perhaps be the
best of both worlds: you'd be guaranteed access to all auto
variables and parameters, and that access wouldn't need to be
tied to the function name (which can be both painful and error
prone).


We can talk about compiler directive.

PRAGMA auto_variables_label() -- require function scope only


If we know a list of all auto variables, then it can be on function or
block level - it can create aliases.


Oh, the problem is that if you have an argument with the same name as an 
auto variable you're in trouble.


Probably the easiest thing is to have a scope that sits above the scope 
containing the arguments, and then allow the user to rename both scopes 
if so desired. So in effect you'd end up with


<> -- new scope
DECLARE
FOUND;
etc
BEGIN
  <>
  DECLARE
  argument_1;
  argument_2;
  BEGIN
-- User supplied block goes here, with optional label
  END;
END;

Alternatively, we could do...

<>
DECLARE
FOUND;
etc
BEGIN
  DECLARE-- User's DECLARE
  argument_1;
  argomuent_2;
  -- User supplied declare code
  BEGIN -- User's BEGIN
  
END

That removes one level of nesting. It's probably better to go with the 
first option though, since it's simpler.


In both cases, I'd really like the ability to rename those blocks. 
#pragma would be fine for that.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] createdb warnings on OS X

[Tom Lane]

Jim Nasby  writes:
> I'm seeing the following warnings on a recent checkout. make check still 
> passes fine though...

There's something screwy about your readline installation (or, if you
didn't install GNU readline, about the libedit that OS X supplies).
This is kind of par for the course on OS X, of course.

>> decibel@decina:[17:04]~/pgsql/HEAD (temp *$%)$uname -a
>> Darwin decina.local 15.6.0 Darwin Kernel Version 15.6.0: Wed Nov  2 20:30:56 
>> PDT 2016; root:xnu-3248.60.11.1.2~2/RELEASE_X86_64 x86_64

I can never remember how Darwin kernel versions map to OS X versions?
But the real question is whether you've got (any vestiges of) a manual
readline installation.

regards, tom lane


-- 
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] RustgreSQL

[Jan de Visser]

On Monday, January 9, 2017 7:06:21 AM EST Craig Ringer wrote:
> On 9 Jan. 2017 05:51, "Jan de Visser"  wrote:
> 
> On Sunday, January 8, 2017 6:28:17 AM EST Joel Jacobson wrote:
> > I don't want to learn the complicated details of C, that's true.
> 
> And this is where I think you're wrong, and why conversion would be hard. C
> has very few complicated details. I don't think it has any, frankly.
> 
> 
> Oh, that's really rather optimistic.
> 
[snip]

Allow me to be snarky and summarize your (very true) points as: "writing 
complicated software systems is hard". 

That's not the fault of the language (in most cases). The complexity can 
somewhat be abstracted by the language, which Rust and Java try to do, or 
completely left to the design, as you're forced to do in C. The former gives 
you either a more complicated language or severly limit what you can do, and 
in the end you will find walls to bang your head against and edges of cliffs to 
fall off of.

The latter, of course, makes your head explode if your system is large enough 
and you don't have Tom Lane and Robert Haas around.



-- 
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] RustgreSQL

[Greg Stark]

On 8 January 2017 at 21:50, Jim Nasby  wrote:
> Somewhat related to that... it would be useful if Postgres had "fenced"
> functions; functions that ran in a separate process and only talked to a
> backend via a well defined API (such as libpq). There's two major advantages
> that would give us:

The problem with this is that any of the "interesting" extensions need
to use the server API. That is, they need to be able to do things like
throw errors, expand toast data, etc.

IMHO just about anything you could do in an external process would be
something you could much more easily and conveniently do in the
client. And it would be more flexible and scalable as well as it's a
lot easier to add more clients than it is to scale up the database.

That said, there were several pl language implementations that worked
this way. IIRC one of the Java pl languages ran in a separate Java
process.

I think the solution to the problem you're describing is the project
formerly known as NaCl
https://en.wikipedia.org/wiki/Google_Native_Client

-- 
greg


-- 
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] RustgreSQL

[Andrew Dunstan]

On 01/08/2017 09:19 AM, Craig Ringer wrote:
On 8 Jan. 2017 17:10, "Joel Jacobson" > wrote:



Is this completely unrealistic or is it carved in stone PostgreSQL
will always be a C project forever and ever?


Incredibly unrealistic.

[lots of other stuff I agree with]

You'd have a lot more chance writing extensions in Rust though. If you 
can make it play OK with the exception handling in postgres and our 
memory management, at least. If you really wanted to push this 
forward... start there. Show how great it is.







Yeah. A few years ago Tom Dunstan and I started creating a sample 
extension in Rust while he was here on a short visit. We ran out of time 
so we didn't quite get it finished. Bottom line is it's possible but far 
from straightforward. Rust's inbuilt build system makes life, er, 
interesting. The fun of getting PG_MODULE_MAGIC in was one of the things 
we had to deal with. We ended up using a small amount of C glue.


cheers

andrew

--
Andrew Dunstanhttps://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



--
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] merging some features from plpgsql2 project

[Jim Nasby]

On 1/7/17 11:44 PM, Pavel Stehule wrote:

This is not overloading of SQL command - it is like annotations. It is
smart idea, so I was not surprised if ANSI/SQL reuses it.


SHas ANSI declared that they will NEVER support := in a SELECT that's 
not running in a stored function? Because if they haven't done that, 
there's nothing preventing them from doing just that. If that happens 
we're going to have some very difficult choices to make.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] merging some features from plpgsql2 project

[Jim Nasby]

On 1/8/17 2:52 AM, Joel Jacobson wrote:

And please kill all these GUCs ideas. The best thing with PostgreSQL
is the natural expected behaviour of the default configuration.
Contrary to MySQL where you have to enable lots and lots of
configuration options just to get a behaviour you expect as a novice
user.


The only reason to use GUCs or some other kind of backwards 
compatibility setting would be to allow the current plpgsql itself to 
move forwards. If you think that's a dead end (which I can certainly 
understand) then they make no sense at all.



It's much better to just come together and agree on whatever we have
learned during the last 15 years of PL/pgSQL1, and sample all ideas
during a year maybe, and decide what to put into PL/pgSQL2. To make it
useful, we should aim to not break compatibility for _most_ code, but
accept some necessary rewrites of functions with deprecated
anti-patterns.


If we're going to create a brand new language then I think it would be 
extremely foolish to keep *any* of the current pain points around. Off 
the top of my head:


- variables must have an identifier (what $ in most languages does). The 
steps you have to go through to avoid simple naming collisions are insane.


- Support for composite types needs to be stronger. Off the top of my 
head, you need to be able to reference an element name via a variable. 
OR, maybe it'd be better to just provide a plpgsql equivalent to a dict.


- GET DIAGNOSTICS and their ilk need to die. There needs to be an easier 
way to get that kind of info back (perhaps via an automatic 
composite/record/dict).


- There needs to be real support for dealing with exceptions. IE: get a 
composite of all exception deatils, modify parts of it, then re-raise 
with the new info.


- Real support for using variables as identifiers / nothing restricted 
to only accepting a Const.


- Support for the notion of a variable being unset (which is NOT the 
same thing as NULL).



That said, I'll bet we still get some of that wrong, so there better be 
some way of fixing those issues down the road...

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] RustgreSQL

[Jim Nasby]

On 1/8/17 5:06 PM, Craig Ringer wrote:

It is very hard to write C that is generally portable, robust in the
face of various edge-case inputs and environmental conditions, are free
from race conditions and memory ordering problems, and rely on no
undefined behaviour.


BTW, if you s/memory/set/ then that exactly describes building 
non-trivial systems on top of relational databases. The devil is always 
in the details.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] createdb warnings on OS X

[Jim Nasby]

I'm seeing the following warnings on a recent checkout. make check still 
passes fine though...



ccache clang -Qunused-arguments -fcolor-diagnostics -Wall -Wmissing-prototypes 
-Wpointer-arith -Wdeclaration-after-statement -Wendif-labels 
-Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv 
-Wno-unused-command-line-argument -O2 -I../../../src/interfaces/libpq 
-I../../../src/include -I/opt/local/include/libxml2   -c -o createdb.o 
createdb.c -MMD -MP -MF .deps/createdb.Po
input.c:81:3: warning: implicit declaration of function 'rl_reset_screen_size' 
is invalid in C99 [-Wimplicit-function-declaration]
rl_reset_screen_size();
^
input.c:455:13: warning: implicit declaration of function 'append_history' is 
invalid in C99 [-Wimplicit-function-declaration]
errnum = append_history(nlines, fname);
 ^
2 warnings generated.



decibel@decina:[17:04]~/pgsql/HEAD (temp *$%)$uname -a
Darwin decina.local 15.6.0 Darwin Kernel Version 15.6.0: Wed Nov  2 20:30:56 
PDT 2016; root:xnu-3248.60.11.1.2~2/RELEASE_X86_64 x86_64
decibel@decina:[17:04]~/pgsql/HEAD (temp *$%)$echo $CC
ccache clang -Qunused-arguments -fcolor-diagnostics
decibel@decina:[17:04]~/pgsql/HEAD (temp *$%)$head config.log|grep './conf'
  $ ./configure --with-libxml --with-tcl --with-perl --with-python 
--enable-depend --enable-dtrace --enable-tap-tests 
--prefix=/Users/decibel/pgsql/HEAD/i/i --with-pgport= -C
decibel@decina:[17:05]~/pgsql/HEAD (temp *$%)$



grep HAVE_RL_RESET_SCREEN_SIZE config.log
| #define HAVE_RL_RESET_SCREEN_SIZE 1
#define HAVE_RL_RESET_SCREEN_SIZE 1
decibel@decina:[17:08]~/pgsql/HEAD (temp *$%)$grep HISTORY config.log
| #define HAVE_READLINE_HISTORY_H 1
| #define HAVE_APPEND_HISTORY 1
| #define HAVE_HISTORY_TRUNCATE_FILE 1
#define HAVE_READLINE_HISTORY_H 1
#define HAVE_APPEND_HISTORY 1
#define HAVE_HISTORY_TRUNCATE_FILE 1



git ls -1
7c3abe3c92 (HEAD -> temp, origin/master, origin/HEAD, master) Get rid of 
ParseState.p_value_substitute; use a columnref hook instead. [Tom Lane]



--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] RustgreSQL

[Craig Ringer]

On 9 Jan. 2017 05:51, "Jan de Visser"  wrote:

On Sunday, January 8, 2017 6:28:17 AM EST Joel Jacobson wrote:
> I don't want to learn the complicated details of C, that's true.

And this is where I think you're wrong, and why conversion would be hard. C
has very few complicated details. I don't think it has any, frankly.


Oh, that's really rather optimistic.

C is a small-ish language. But achieving a good understanding of its memory
model and its implications isn't easy at all. There is lots of undefined
behaviour in the language too, which makes it easy to write code that
works... mostly. Usually. Until you hit some edge case, run on a different
architecture/platform, etc.

Then the system libraries and their implementations add complexity. ptheads
may not be part of C but for many projects a solid understanding of them is
crucial... and not that easy.

Do you comprehensively understand the rules for memory ordering when
processes interact in shared memory? Can you explain the correct uses of
volatile and when declaring something volatile is / isn't sufficient for
ensuring safe concurrent access? What happens if you dereference a pointer
to a struct allocated on the stack of a just-returned function?

I had a quick look at Rust and it sounds like it tries to make this sort of
stuff simpler. I didn't see any formal definition of a memory model though
- it seems like it figures you'll just use its concurrency primitives. And
C looks simple enough at first too... emergent complexity from seemingly
simple rules is hard.

It is easy to write C programs of moderate complexity that work reliably
within tested conditions and are somewhat portable to a set of tested-for
architectures. It is very hard to write C that is generally portable,
robust in the face of various edge-case inputs and environmental
conditions, are free from race conditions and memory ordering problems, and
rely on no undefined behaviour.

This is only partly a deficiency of C. Lots of it is down to low level
systems being complex, hard and varied. Weak vs strong memory ordering,
LP64 vs ILP64, etc etc etc.

I know only just enough C to be dangerous. Admittedly I haven't adequately
studied the language, but I have some idea how much I don't know. I doubt
there are a lot of people who can write truly error-free C. But that's also
true of pretty much any language, even ones that purport to be safe.

Re: [HACKERS] RustgreSQL

[Gavin Flower]

On 09/01/17 11:31, Michael Paquier wrote:

On Mon, Jan 9, 2017 at 6:51 AM, Jan de Visser  wrote:

Your fear of C in unfounded. The definitive c89 reference is a little book of
about 250 pages, more than half of which is about the standard library of
which you'll never use more than half. If you have some notepaper laying about
on which to scribble pointer diagrams you can be a C programmer to :-)

The reference guide of Brian Kernighan and Dennis Ritchie? Definitely
a must-have!


I learnt C from the original version of K, and bought the ANSI version 
when that came out - at the time I was a COBOL programmer on a mighty 
MainFrame (with a 'massive' 1 MB of Core Memory and a 'fast' 2 MHz 
processor).  Now I use Java.



Cheers,
Gavin



--
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] RustgreSQL

[Peter Geoghegan]

On Sun, Jan 8, 2017 at 1:51 PM, Jan de Visser  wrote:
> And this is where I think you're wrong, and why conversion would be hard. C
> has very few complicated details. I don't think it has any, frankly. It
> basically says "If you want your datastructure to outlive a function call,
> I'll give you a chunk of memory and you're now responsible for it. Have fun".
> That's not complicated: it's two functions, malloc() and free(), basically.

I don't think that that is true in practice. This paper summarizes why
this is the case: https://www.cl.cam.ac.uk/~pes20/cerberus/pldi16.pdf

At the same time, I don't think it would be a good idea to adopt Rust
for Postgres development, and not purely because of our legacy (it
might be interesting as a language for extensions, however). The
contradictory goals of C are what results in the kind of ambiguity
that that paper goes into. C may have contradictory goals, but that
doesn't mean they're the wrong goals, even when considered as a whole.
The culture that C is steeped in still makes a lot of sense for a
system like Postgres.

-- 
Peter Geoghegan


-- 
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] RustgreSQL

[Michael Paquier]

On Mon, Jan 9, 2017 at 6:51 AM, Jan de Visser  wrote:
> Your fear of C in unfounded. The definitive c89 reference is a little book of
> about 250 pages, more than half of which is about the standard library of
> which you'll never use more than half. If you have some notepaper laying about
> on which to scribble pointer diagrams you can be a C programmer to :-)

The reference guide of Brian Kernighan and Dennis Ritchie? Definitely
a must-have!
-- 
Michael


-- 
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] RustgreSQL

[Jan de Visser]

On Sunday, January 8, 2017 6:28:17 AM EST Joel Jacobson wrote:
> I don't want to learn the complicated details of C, that's true.

And this is where I think you're wrong, and why conversion would be hard. C 
has very few complicated details. I don't think it has any, frankly. It 
basically says "If you want your datastructure to outlive a function call, 
I'll give you a chunk of memory and you're now responsible for it. Have fun". 
That's not complicated: it's two functions, malloc() and free(), basically. 

What's hard and complicated is keeping track of all those little chunks of 
memory you have laying around. That management is deeply intertwined with the 
algorithmics of the system, and separating memory management from the actual 
work done will be very hard. In many cases the algorithm will have been 
implemented with cheap memory management in mind, and porting it to a 
different paradigm (garbage collection or rust's reference ownership) can 
result in either a lot of work and probably bugs, or bad performing code.

I personally find keeping track of allocated memory easier than rust's 
convoluted (to me) rules regarding reference ownership.

Your fear of C in unfounded. The definitive c89 reference is a little book of 
about 250 pages, more than half of which is about the standard library of 
which you'll never use more than half. If you have some notepaper laying about 
on which to scribble pointer diagrams you can be a C programmer to :-)



-- 
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] RustgreSQL

[Jim Nasby]

On 1/8/17 10:28 AM, Joel Jacobson wrote:

You'd have a lot more chance writing extensions in Rust though. If you can make 
it play OK with the exception handling in postgres and our memory management, 
at least. If you really wanted to push this forward... start there. Show how 
great it is.

Funny you mention, that's actually exactly how these thoughts started
in my head. I realized I would need to write a C function to do some
computations that needed to be fast, but since C is dangerous I don't
know the language well enough I thought "I wish there was a safe and
fast system language you could
write database functions in!", and that's how I first found Rust and
then later this project:
https://github.com/thehydroimpulse/postgres-extension.rs
Which allows doing exactly that, writing extensions in Rust.


Somewhat related to that... it would be useful if Postgres had "fenced" 
functions; functions that ran in a separate process and only talked to a 
backend via a well defined API (such as libpq). There's two major 
advantages that would give us:


- Untrusted languages could be made trusted. Currently there's no way to 
limit what a user could do with a function like plpythonu or pl/r, and 
there's no good way to make either of those languages trusted. But if 
the function code was running in a separate process, that process could 
be owned by a user and group (ie: nobody:nogroup) that has no 
permissions to do anything at the OS level. This would make it possible 
for hosting platforms like RDS to offer these languages without undue 
risk. I believe Joe Conway has done some investigation in this area.


- If you had a buggy C function that crashed it wouldn't force us to 
panic the entire database. The fenced process would die, we'd detect 
that and just through a normal error. Currently, C functions can access 
*everything* in backend memory, including shared memory. That's why if 
one crashes we have to panic. Even worse, a bug that corrupts data but 
doesn't always crash could do enormous amounts of damage. The worst a 
bug in a fenced function could do is screw up whatever it's returning to 
the backend.


As for Postgres in general, I think it would be nice if it was easier to 
do some things in a language other than C, for code simplicity reasons. 
The issue with that though is not significantly expanding what's 
necessary to build Postgres. I doubt that rust would meet that criteria.


That said, the community will at least consider things that offer 
*significant* advantages. For example, there's been some discussion 
about making use of LLVM for the executor. Since LLVM is meant to make 
it easier to build new languages, it's possible that it could be used to 
simplify other pieces of code as well.


BTW, I just came across http://safecode.cs.illinois.edu; that might get 
you a lot of the benefits you're looking for in Rust. The description on 
http://llvm.org claims it can be used like Valgrind, which the project 
currently supports.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] Re: Clarifying "server starting" messaging in pg_ctl start without --wait

[Jim Nasby]

On 1/7/17 11:14 PM, Ryan Murphy wrote:

So I realized that I've never actually done `make world` before, and
when I try that I get a funny error:

make -C doc all
make -C src all
make -C sgml all

...

***
ERROR: `osx' is missing on your system.

...

Do you have any idea what that means?  I googled it but couldn't find
anything.  I can dig around more or ask the mailing list if you have no
idea.


It's good to reply on the list (which I've cc'd) as there's lots of 
folks that can help you that way.


To answer your question, the error has something to do with building 
docs. You don't really need to do that, and it can be rather painful to 
get setup to build them. I wouldn't bother for now.



But anyway, last time I think I was running `make installworld-check`
without first running `make world`, which I think is wrong right? - need
`make world` first?


No, you don't. Some of our make targets can be a bit confusing in this 
regard...


installworld-check will install all code (and docs, if they've been 
built) and then run full tests against them. There's no tests for the 
docs, so it doesn't matter if they get installed. The only "test" for 
docs is whether they build, but IMHO it's not worth it to ask a new 
reviewer to try and test that unless the patch has a *lot* of changes to 
the docs.


In any case, docs won't explain why you were seeing a test failure and I 
wasn't.


Hmm... I just thought of something though... do you have PGUSER set? 
That might break installworld-check.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
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] sequence data type

[Steve Singer]

On 12/31/2016 01:27 AM, Peter Eisentraut wrote:
Another updated patch, with quite a bit of rebasing and some minor 
code polishing.




Patch applies cleanly and the tests pass
The feature seems to work as expected.

I've tried this out and it behaves as expected and desired. I also 
tested the PG dump changes when dumping from both 8.3 and 9.5 and tables 
with serial types and standalone sequences restore as I would expect.



The only concern I have with the functionality is that there isn't a way 
to change the type of a sequence.


For example

create table foo(id serial4);
--hmm I"m running out of id space
alter table foo alter column id type int8;
alter sequence foo_id_seq maxvalue
9223372036854775807;

2017-01-08 14:29:27.073 EST [4935] STATEMENT:  alter sequence foo_id_seq 
maxvalue

9223372036854775807;
ERROR:  MAXVALUE (9223372036854775807) is too large for sequence data 
type integer


Since we allow for min/maxvalue to be changed I feel we should also 
allow the type to be changed.





@@ -1236,7 +1239,15 @@ init_params(ParseState *pstate, List *options, 
bool isInit,

{
DefElem*defel = (DefElem *) lfirst(option);

-   if (strcmp(defel->defname, "increment") == 0)
+   if (strcmp(defel->defname, "as") == 0)
+   {
+   if (as_type)
+   ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+errmsg("conflicting or 
redundant options")));

+   as_type = defel;
+   }
+   else if (strcmp(defel->defname, "increment") == 0)

Should we including parser_errposition(pstate, defel->location)));  like 
for the other errors below this?



Other than that the patch looks good



--
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] PATCH: recursive json_populate_record()

[Tom Lane]

Nikita Glukhov  writes:
> [ 0001_recursive_json_populate_record_v02.patch ]
> [ 0002_assign_ndims_to_record_function_result_types_v02.patch ]

I do not see the point of the second one of these, and it adds no test
case showing why it would be needed.  The example you quoted at the
start of the thread doesn't fail for me in HEAD, so I surmise that
it's falling foul of some assertion you added in the 0001 patch, but
if so I think that assertion is wrong.  attndims is really syntactic
sugar only and doesn't affect anything meaningful semantically.  Here
is an example showing why it shouldn't:

regression=# create table foo (d0 _int4, d1 int[], d2 int[3][4]);
CREATE TABLE
regression=# select attname,atttypid,attndims from pg_attribute where attrelid 
= 'foo'::regclass and attnum > 0;
 attname | atttypid | attndims 
-+--+--
 d0  | 1007 |0
 d1  | 1007 |1
 d2  | 1007 |2
(3 rows)

Columns d0,d1,d2 are really all of the same type, and any code that
treats d0 and d1 differently is certainly broken.

So there should be no need to track a particular attndims for an output
column of a function result, and in most cases it's not clear to me where
you would get an attndims value from anyway.

regards, tom lane


-- 
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] Explicit subtransactions for PL/Tcl

[Tom Lane]

Victor Wagner  writes:
> I'm attaching the patch which implements subtransaction command for
> PL/Tcl which does almost same as PL/Python plpy.subtransction context
> manager object does: executes a block of Tcl code in the context of
> subtransaction, rolls subtransaction back if error occures and commits
> it otherwise.

Interesting.  Please add this to the 2017-03 commitfest list, to make
sure we don't lose track of it.

regards, tom lane


-- 
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] Explicit subtransactions for PL/Tcl

[Victor Wagner]

On Sun, 8 Jan 2017 20:57:50 +0300
Victor Wagner  wrote:

> Collegues!
> 
> Recently I've found out that PL/Python have very nice feature -
> explicit subtransaction object, which allows to execute block of code
> in the context of subtransaction.
> 
[skip]

> 
> I'm attaching the patch which implements subtransaction command for

Sorry, unfortunately attached empty file instead of patch
-- 
   Victor Wagner 
diff --git a/doc/src/sgml/pltcl.sgml b/doc/src/sgml/pltcl.sgml
index 8afaf4a..7a532b7 100644
--- a/doc/src/sgml/pltcl.sgml
+++ b/doc/src/sgml/pltcl.sgml
@@ -901,6 +901,85 @@ if {[catch { spi_exec $sql_command }]} {
  is a global variable.)
 

+   
+   Explicit Subtransactions
+  
+   Recovering from errors caused by database access as described in
+can lead to an undesirable
+   situation where some operations succeed before one of them fails,
+   and after recovering from that error the data is left in an
+   inconsistent state.  PL/Tcl offers a solution to this problem in
+   the form of explicit subtransactions.
+  
+
+  
+   Subtransaction command
+
+   
+Consider a function that implements a transfer between two
+accounts:
+
+CREATE FUNCTION transfer_funds() RETURNS void AS $$
+if [catch {
+spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
+spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
+} errormsg] {
+set result [format "error transferring funds: %s" $errormsg ]
+} else {
+set result "funds transferred correctly"
+}
+set plan [spi_prepare "INSERT INTO operations (result) VALUES ($1)"]
+spi_execp -count 1 $plan, [list $result)
+$$ LANGUAGE pltclu;
+
+If the second UPDATE statement results in an
+exception being raised, this function will report the error, but
+the result of the first UPDATE will
+nevertheless be committed.  In other words, the funds will be
+withdrawn from Joe's account, but will not be transferred to
+Mary's account.
+   
+
+   
+To avoid such issues, you can wrap your
+spi_exec calls in an explicit
+subtransaction.  The PL/Tcl provides a
+commmand subtransaction to manage explicit
+	subtransactions.
+ Using explicit subtransactions
+we can rewrite our function as:
+
+CREATE FUNCTION transfer_funds2() RETURNS void AS $$
+if [catch {
+	subtransaction {
+spi_exec "UPDATE accounts SET balance = balance - 100 WHERE account_name = 'joe'"
+spi_exec "UPDATE accounts SET balance = balance + 100 WHERE account_name = 'mary'"
+	}
+	} errormsg] {
+		set result [format "error transferring funds: %s" $errormsg]
+	} else {
+   set result "funds transferred correctly"
+	}
+set plan  [spi_prepare "INSERT INTO operations (result) VALUES ($1)"]
+spi_execp $plan, [list $result]
+$$ LANGUAGE pltclu;
+
+Note that the use of catch is still
+required.  Otherwise the exception would propagate to the top of
+the  stack and would cause the whole function to abort with
+a PostgreSQL error, so that the
+operations table would not have any row
+inserted into it.  The subtransaction command does not
+trap errors, it only assures that all database operations executed
+inside its scope will be atomically committed or rolled back.  A
+rollback of the subtransaction block occurs on any kind of
+exception exit, not only ones caused by errors originating from
+database access.  A regular Tcl exception raised inside an
+explicit subtransaction block would also cause the subtransaction
+to be rolled back.
+   
+  
+  
 

Modules and the unknown Command
diff --git a/src/pl/tcl/Makefile b/src/pl/tcl/Makefile
index 25082ec..614385d 100644
--- a/src/pl/tcl/Makefile
+++ b/src/pl/tcl/Makefile
@@ -28,7 +28,7 @@ DATA = pltcl.control pltcl--1.0.sql pltcl--unpackaged--1.0.sql \
pltclu.control pltclu--1.0.sql pltclu--unpackaged--1.0.sql
 
 REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-extension=pltcl
-REGRESS = pltcl_setup pltcl_queries pltcl_unicode
+REGRESS = pltcl_setup pltcl_queries pltcl_unicode pltcl_subxact
 
 # Tcl on win32 ships with import libraries only for Microsoft Visual C++,
 # which are not compatible with mingw gcc. Therefore we need to build a
diff --git a/src/pl/tcl/expected/pltcl_subxact.out b/src/pl/tcl/expected/pltcl_subxact.out
new file mode 100644
index 000..17b9d90
--- /dev/null
+++ b/src/pl/tcl/expected/pltcl_subxact.out
@@ -0,0 +1,145 @@
+--
+-- Test explicit subtransactions
+--
+-- Test table to see if transactions get properly rolled back
+CREATE TABLE subtransaction_tbl (
+i integer
+);
+CREATE FUNCTION subtransaction_ctx_test(what_error text = NULL) RETURNS text
+AS $$
+subtransaction {
+spi_exec "INSERT INTO subtransaction_tbl VALUES (1)"
+spi_exec "INSERT INTO subtransaction_tbl VALUES (2)"
+if {$1 == "SPI"} {
+spi_exec "INSERT INTO 

[HACKERS] Explicit subtransactions for PL/Tcl

[Victor Wagner]

Collegues!

Recently I've found out that PL/Python have very nice feature - explicit
subtransaction object, which allows to execute block of code in the
context of subtransaction.

I've quite surprised that other PL languages, shipped with PostgreSQL do
not have such useful construction. 

If it might require considerable trickery to add such functionality into
PL/Perl, Tcl allows to add new control stuctures very easily.

I'm attaching the patch which implements subtransaction command for
PL/Tcl which does almost same as PL/Python plpy.subtransction context
manager object does: executes a block of Tcl code in the context of
subtransaction, rolls subtransaction back if error occures and commits
it otherwise.

It looks like

subtransaction {
 ...some Tcl code...
}

Typically one would use it inside Tcl catch statement:

if [catch {
subtransaction {
spi_exec "insert into..."
...
}
} errormsg] {
   # Handle an error
}

See documentation and tests included in the patch for more complete
examples.

Just like corresponding Python construction, this command doesn't
replace language  builtin exception handling, just adds subtransaction 
support to it.

Patch includes sufficiently less tests than python subtransaction tests,
because Tcl implementation is way simpler than python one, and doesn't
have syntactic variatons which depend on language version.

Also entering and exiting subtransactions are in the same piece of code
rather than in separate __enter__ and __exit__ methods as in Python, so
there is no possibility to call exit without enter or vice versa.

-- 
   Victor Wagner 

-- 
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] [PATCH] guc-ify the formerly hard-coded MAX_SEND_SIZE to max_wal_send

[Greg Stark]

On 8 January 2017 at 17:26, Greg Stark  wrote:
> On 5 January 2017 at 19:01, Andres Freund  wrote:
>> That's a bit odd - shouldn't the OS network stack take care of this in
>> both cases?  I mean either is too big for TCP packets (including jumbo
>> frames).  What type of OS and network is involved here?
>
> 2x may be plausible. The first 128k goes out, then the rest queues up
> until the first ack comes back. Then the next 128kB goes out again
> without waiting... I think this is what Nagle is supposed to actually
> address but either it may be off by default these days or our usage
> pattern may be defeating it in some way.

Hm. That wasn't very clear.  And the more I think about it, it's not right.

The first block of data -- one byte in the worst case, 128kB in our
case -- gets put in the output buffers and since there's nothing
stopping it it immediately gets sent out. Then all the subsequent data
gets put in output buffers but buffers up due to Nagle. Until there's
a full packet of data buffered, the ack arrives, or the timeout
expires, at which point the buffered data drains efficiently in full
packets. Eventually it all drains away and the next 128kB arrives and
is sent out immediately.

So most packets are full size with the occasional 128kB packet thrown
in whenever the buffer empties. And I think even when the 128kB packet
is pending Nagle only stops small packets, not full packets, and the
window should allow more than one packet of data to be pending.

So, uh, forget what I said. Nagle should be our friend here.

I think you should get network dumps and use xplot to understand
what's really happening. c.f.
https://fasterdata.es.net/assets/Uploads/20131016-TCPDumpTracePlot.pdf


-- 
greg


-- 
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] [PATCH] guc-ify the formerly hard-coded MAX_SEND_SIZE to max_wal_send

[Greg Stark]

On 5 January 2017 at 19:01, Andres Freund  wrote:
> That's a bit odd - shouldn't the OS network stack take care of this in
> both cases?  I mean either is too big for TCP packets (including jumbo
> frames).  What type of OS and network is involved here?

2x may be plausible. The first 128k goes out, then the rest queues up
until the first ack comes back. Then the next 128kB goes out again
without waiting... I think this is what Nagle is supposed to actually
address but either it may be off by default these days or our usage
pattern may be defeating it in some way.

-- 
greg


-- 
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] Increase pltcl test coverage

[Tom Lane]

Jim Nasby  writes:
> On 1/6/17 2:17 PM, Tom Lane wrote:
>> This is in a format that neither patch(1) nor "git apply" recognize.
>> Please resubmit in a more usual format, diff -c or diff -u perhaps.

> Odd, dunno what happened there. New patch attached.

This applies, but I fail to get the expected regression test output:

*** /home/postgres/pgsql/src/pl/tcl/expected/pltcl_queries.out  Sun Jan  8 
11:54:19 2017
--- /home/postgres/pgsql/src/pl/tcl/results/pltcl_queries.out   Sun Jan  8 
12:18:27 2017
***
*** 515,529 
  select tcl_eval('spi_prepare a "b {"');
  ERROR:  unmatched open brace in list
  select tcl_error_handling_test($tcl${ spi_prepare "moo" }$tcl$);
!tcl_error_handling_test
! --
!  COMMAND:  spi_prepare "moo" +
!  POSTGRES: 'POSTGRES'+
!  TCL: LOOKUP +
!  funcname: 'funcname'+
!  lineno: 'lineno'
! (1 row)
! 
  -- test full error text
  select tcl_error_handling_test($tcl$
  spi_exec "DO $$
--- 515,521 
  select tcl_eval('spi_prepare a "b {"');
  ERROR:  unmatched open brace in list
  select tcl_error_handling_test($tcl${ spi_prepare "moo" }$tcl$);
! ERROR:  list must have an even number of elements
  -- test full error text
  select tcl_error_handling_test($tcl$
  spi_exec "DO $$


Investigation shows that $::errorCode contains just "NONE", which
is why the "array set myArray $::errorCode" is blowing up.  And
that's sort of what I'd expect, because $err is
invalid command name " spi_prepare "moo" "
which is not a Postgres-detected error.  So probably the example
tcl_error_handling_test function should not dispense with the
if {[lindex $::errorCode 0] == "POSTGRES"}
guard that we recommend in the manual.  But I don't understand
how you got the sample output shown in the patch.  Is this based
on some unsubmitted changes in pltcl's error handling?

regards, tom lane


-- 
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] RustgreSQL

[Joel Jacobson]

Thank you Craig for explaining the current C state of the project,
very interesting to learn about.

On Sun, Jan 8, 2017 at 4:19 AM, Craig Ringer
 wrote:
> If a low pain seamless conversation/adaptation like that were possible I'd 
> have to wonder what Rust could actually offer us over C since it clearly has 
> the same scope for issues if such an intermixture is possible. Kind of a 
> catch-22.

Not necessarily. If you write non-idiomatic Rust and preserve as much
of the code style as possible from the original C code, and just focus
on getting ride of all usage of unsafe, then it will be more easily
understandable by existing C developers than if strictly writing
idiomatic Rust code.

> You'd have a lot more chance writing extensions in Rust though. If you can 
> make it play OK with the exception handling in postgres and our memory 
> management, at least. If you really wanted to push this forward... start 
> there. Show how great it is.

Funny you mention, that's actually exactly how these thoughts started
in my head. I realized I would need to write a C function to do some
computations that needed to be fast, but since C is dangerous I don't
know the language well enough I thought "I wish there was a safe and
fast system language you could
write database functions in!", and that's how I first found Rust and
then later this project:
https://github.com/thehydroimpulse/postgres-extension.rs
Which allows doing exactly that, writing extensions in Rust.

> You don't want to to learn C so dozens/hundreds of people need to learn Rust. 
> What the?

Oh, I don't think you seriously think I meant to suggest others should
learn Rust just because I don't want to learn C.
I don't want to learn the complicated details of C, that's true.
But that has nothing to do why others would need to learn Rust. They
don't, unless the majority of the project would also want to move to
Rust, and that has of course nothing to do with me.
I'm just asking possibly stupid questions and having possibly stupid
theories, trying to understand why such a project would be possible or
not.


-- 
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] RustgreSQL

[Craig Ringer]

Is this completely unrealistic or is it carved in stone PostgreSQL will
always be a C project forever and ever?


Another thing to look at if you want to approach this as a serious,
practical effort is the atomics,  memory barrier, spinlock and lwlock code.

I just don't see it happening.

Re: [HACKERS] RustgreSQL

[Craig Ringer]

On 8 Jan. 2017 18:14, "Fabien COELHO"  wrote:


Is this completely unrealistic or is it carved in stone PostgreSQL will
>> always be a C project forever and ever?
>>
>
> From my very limited understanding, PostgreSQL is more likely to be
> converted to C++!
>

ISTM that currently pg is written C89. Personnaly I think that C99
(standard from 18 years ago...) would be a progress, but this has been
rejected in the past because of portability issues on some platforms (eg MS
Visual C++ started to support part of C99 in ... 2013).


MSVC was really the main issue. MS really insisted that C++ was the future
and C99 was a pointless diversion.

I kinda agree with them TBH, albeit with a preference for a small-ish and
carefully used subset of C++. But they've recognised that it matters to
enough people to add support now anyways. I suspect their increased
interest in open source and Linux is related.

Re: [HACKERS] RustgreSQL

[Craig Ringer]

On 8 Jan. 2017 17:10, "Joel Jacobson"  wrote:


Is this completely unrealistic or is it carved in stone PostgreSQL will
always be a C project forever and ever?


Incredibly unrealistic.

PostgreSQL makes heavy use of variable length arrays. longjmp() is critical
to its error handling. Lots of other "unsafe" things.

More to the point though, we struggle to get any acceptance of the most
trivial added dependences. Even optionally. We still support a
more-than-10-year-old Perl. We still use C89. The project is VERY
conservative and expects new releases to run on ancient UNIXes long
forgotten by the rest of the world.

This has some advantages. Those weird compilers and platforms help catch
bugs. Most of the time they cost us little but minor inconvenience and
they're part of why some contributors stay around.

But it also makes it way harder to make significant change.

IMO the chances of the project switching to Rust are about as high as
Oracle Database going open source, or MongoDB declaring that it's changing
to SQL as the primary and preferred query language.

The ONLY way I could imagine it happening would be if you could show that
it could be done incrementally, in a way that retained support for 10+ year
old platforms, with a significant increase in performance and decrease in
code size/complexity for converted modules. With minimal or no "rust
droppings" (macros everywhere etc) to help such an incremental adaptation
along. Even then you'd have to convince a lot of people who know C well (or
well enough, or think they do) that it was worth such massive change. If a
low pain seamless conversation/adaptation like that were possible I'd have
to wonder what Rust could actually offer us over C since it clearly has the
same scope for issues if such an intermixture is possible. Kind of a
catch-22.

A restricted subset of C++ is a lot more likely. Even then longjmp will
cause us pain... I suspect we'd land up having to move to C++ exceptions.

Take a look at elog.c, the memory contexts code, etc. If you think Rust can
play well with that, cool. I can't imagine how though.

You'd have a lot more chance writing extensions in Rust though. If you can
make it play OK with the exception handling in postgres and our memory
management, at least. If you really wanted to push this forward... start
there. Show how great it is.

Then come up with a plan for how you'd handle existing extensions
(PostGIS?), external PLs, ecpg, pgxs, etc. Make sure libpq stays totally
compatible. All that fun.

I just don't see it happening. Not do I see you suggesting any possible
reason why we'd care or want to. You don't want to to learn C so
dozens/hundreds of people need to learn Rust. What the?

Re: [HACKERS] RustgreSQL

[Greg Sabino Mullane]

-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160


Joel Jacobson asked:

> Is anyone working on porting PostgreSQL to Rust?

No; extremely unlikely.

> My motivation is primarily I don't want to learn all the 
> over-complicated details of C

Well that's going to be a show-stopper right there. For a proper 
port, a deep understanding of the current source code is necessary.
You'd need a team expert in both C and Rust to pull it off.

> Porting PostgreSQL to Rust would be a multi-year project,
> and it could only be done if the process could be fully automated,
> by supporting all the coding patterns used by the project,
> otherwise a Rust-port would quickly fall behind the master branch.
> But if all git commits could be automatically converted to Rust,

Developing such a system is bordering on AI and likely more complex 
than Postgres itself. :)

> Is this completely unrealistic or is it carved in stone PostgreSQL will
> always be a C project forever and ever?

It's unrealistic, but there is nothing to say Postgres will stay in C 
forever. Right now, however, there is no compelling reason to move 
away from it, and the porting effort to any language would be immense. 
C++ would be the least painful option, probably.

- -- 
Greg Sabino Mullane g...@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201701080905
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-

iEYEAREDAAYFAlhyR44ACgkQvJuQZxSWSsimzgCg97QZZ47BfNtema5aoN2QIpY9
wTUAn3B042YDH82GPLDwXmDSgJMzsoGD
=PH10
-END PGP SIGNATURE-




-- 
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] RustgreSQL

[Fabien COELHO]

Is this completely unrealistic or is it carved in stone PostgreSQL will 
always be a C project forever and ever?


From my very limited understanding, PostgreSQL is more likely to be converted 
to C++!


ISTM that currently pg is written C89. Personnaly I think that C99 
(standard from 18 years ago...) would be a progress, but this has been 
rejected in the past because of portability issues on some platforms (eg 
MS Visual C++ started to support part of C99 in ... 2013).


--
Fabien.


--
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] RustgreSQL

[Gavin Flower]

On 08/01/17 22:09, Joel Jacobson wrote:

Hi all,

Is anyone working on porting PostgreSQL to Rust?

Corrode looks a bit limited for the task, but maybe it can be a start.
It doesn't support goto or switch, but maybe the gotos patterns are 
not too complicated.


My motivation is primarily I don't want to learn all the 
over-complicated details of C,
but at the same time I would like to be productive in a safe system 
language,

a category in which Rust seems to be alone.

Porting PostgreSQL to Rust would be a multi-year project,
and it could only be done if the process could be fully automated,
by supporting all the coding patterns used by the project,
otherwise a Rust-port would quickly fall behind the master branch.
But if all git commits could be automatically converted to Rust,
then the RustgreSQL project could pull all commits from upstream
until all development has switched over to Rust among all developers.

Is this completely unrealistic or is it carved in stone PostgreSQL 
will always be a C project forever and ever?


From my very limited understanding, PostgreSQL is more likely to be 
converted to C++!



Cheers,
Gavin



--
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] [PATCH] Generic type subscription

[Dmitry Dolgov]

> On 4 January 2017 at 18:06, Artur Zakirov 
wrote:
> But I'm not convinced about how to distinguish ArrayRef node with new
> SubscriptingRef node.

I'm not sure I understood you correctly. You're talking about having two
nodes
`ArrayRef` and `SubscriptingRef` at the same time for the sake of backward
compatibility, am I right? But they're basically the same, since
`SubscriptingRef` name is used just to indicate more general purpose of this
node.

> Also Tom pointed that he had bad experience with using ArrayRef node:

Yes, but it was related to the idea of having `ArrayRef` and `JsonbRef`
nodes
for specific types. Since now there is generic `SubscriptingRef` node, I
think
it should be ok.

>> Hm...I already answered, that I managed to avoid compilation problems for
>> this particular extension using the `genparser` command again:

> I suppose that a separate node type could solve it.

Just to be clear - as far as I understood, these compilation problems were
caused not because the extension knew something about ArrayRef node in
particular, but because the extension tried to extract all nodes to generate
code from them. It means any change will require "refetching", so I think
it's
natural for this extension.

Re: [HACKERS] proposal: session server side variables (fwd)

[Fabien COELHO]

Hello Bruce,


Good. So we seem to agree that GUCS are transactional?


Uh, I think it is a missing feature, i.e.:

https://wiki.postgresql.org/wiki/Todo#Administration
Have custom variables be transaction-safe
https://www.postgresql.org/message-id/4b577e9f.8000...@dunslane.net


Hmmm, that is a subtle one:-)

After more testing, the current status is that the values of existing 
user-defined parameters is cleanly transactional, as already tested:


 fabien=# SET x.x = 'before';
 fabien=# BEGIN;
 fabien=# SET x.x = 'inside';
 fabien=# ROLLBACK;
 fabien=# SHOW x.x;
 -- 'before'

This is what I meant by "GUCs are transactional".

However, as you point out, the existence of the parameter is not: If it is 
created within an aborted transaction then it still exists afterwards:


 fabien=# SHOW z.z;
 ERROR:  unrecognized configuration parameter "z.z"
 fabien=# BEGIN;
 fabien=# SET z.z = 'yep';
 fabien=# ROLLBACK;
 fabien=# SHOW z.z;
 -- no error, empty string shown

So GUCs are... half-transactional? :-)

From the security-related use case perspective, this half transactionality 
is enough, but it is not very clean. Does not look like a very big issue 
to fix, it just seems that nobody bothered in the last 6 years...


--
Fabien.


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] RustgreSQL

[Joel Jacobson]

Hi all,

Is anyone working on porting PostgreSQL to Rust?

Corrode looks a bit limited for the task, but maybe it can be a start.
It doesn't support goto or switch, but maybe the gotos patterns are not too
complicated.

My motivation is primarily I don't want to learn all the over-complicated
details of C,
but at the same time I would like to be productive in a safe system
language,
a category in which Rust seems to be alone.

Porting PostgreSQL to Rust would be a multi-year project,
and it could only be done if the process could be fully automated,
by supporting all the coding patterns used by the project,
otherwise a Rust-port would quickly fall behind the master branch.
But if all git commits could be automatically converted to Rust,
then the RustgreSQL project could pull all commits from upstream
until all development has switched over to Rust among all developers.

Is this completely unrealistic or is it carved in stone PostgreSQL will
always be a C project forever and ever?

Re: [HACKERS] merging some features from plpgsql2 project

[Joel Jacobson]

On Sat, Jan 7, 2017 at 8:56 PM, Pavel Stehule  wrote:
>
> Jim, Marko, Joel - is there a place, features where we can find a partial 
> agreement? If it is, then we can move our view there.

I have decided I definitively want a new language, and I'm willing to
pay for it.

Hopefully the community will join forces and contribute with ideas and
code, but with or without you or the rest of the community, plpgsql2
is going to happen.
Call it pltrustly or plpgsql2, I don't care. I just care about ending
my suffering from being forced writing plpgsql every day. It sucks,
and I'm going to end it.

I'm just too fed up with the annoyances of plpgsql. I cannot care less
about _hypothetical_ incompatibility problems,
I think your arguments "this is like Perl6 or Python3" are delusional.
You can easily intermix plpgsql and plpgsql2 in the same
"application", something you cannot do with Perl6 or Python3. So
please stop using that as an argument.

If anyone has an application where the hypothetical incompatibility
problems would be a problem, then just continue to use plpgsql.

And please kill all these GUCs ideas. The best thing with PostgreSQL
is the natural expected behaviour of the default configuration.
Contrary to MySQL where you have to enable lots and lots of
configuration options just to get a behaviour you expect as a novice
user.

It's much better to just come together and agree on whatever we have
learned during the last 15 years of PL/pgSQL1, and sample all ideas
during a year maybe, and decide what to put into PL/pgSQL2. To make it
useful, we should aim to not break compatibility for _most_ code, but
accept some necessary rewrites of functions with deprecated
anti-patterns.

I'm even willing to suggest it might be a good idea to first try out
PL/pgSQL2 at Trustly, and after a year of usage, report back to the
community of our findings on how well it worked out for us, to allow
all others to learn from our mistakes during our first year of using
the new language. That way less people and companies will have to
suffer when we discover what we got wrong in what we thought would
work out well for us.

During the same trial period maybe your company Pavel and others can
try out their ideas of a PL/pgSQL2 and implement it, see how it works
out for you, and then report back to the community on your findings
from production environments.

That way we can avoid all these hypothetical discussions on what will
be good or bad without having any empirical evidence at hand.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers