Re: [HACKERS] Immediate standby promotion

[Amit Kapila]

On Mon, Sep 1, 2014 at 4:44 PM, Fujii Masao  wrote:
> On Mon, Sep 1, 2014 at 3:23 PM, Amit Kapila 
wrote:
> > I think there is one downside as well for this proposal that
> > apart from data loss, it can lead to uncommitted data occupying
> > space in database which needs to be later cleaned by vacuum.
> > This can happen with non-immediate promote as well, but the
> > chances with immediate are more.  So the gain we got by doing
> > immediate promotion can lead to slow down of operations in some
> > cases.  It might be useful if we mention this in docs.
>
> Yep, the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data of VACUUM. But, OTOH,
> I think that the immediate promotion might be more likely to cause
> the recovery to end before replaying WAL data which will generate
> garbage data.

This seems arguable, because immediate promotion won't allow
WAL data to be replayed completely which means more chance
that only partial data of transactions will be replayed and commit
for those transactions won't get replayed, so it can lead to garbage
data.

> So I'm not sure if it's worth adding that note to the doc.

No issues, I just want to bring this point to your notice so that if
you think it is important enough that we can mention it then we
can update the docs else leave it.

> >
> > Few comments about patch:
> >
> > 1.
> > On standby we will see below message:
> >
> > LOG:  received promote request
> >
> > User will always see above message irrespective of whether it
> > is immediate promote or any other mode of promote. I think it will
> > be better to distinguish between different modes and display the
> > appropriate message.
>
> Agreed. So I'm thinking to change the code as follows.
>
> if (immediate_promote)
> ereport(LOG, (errmsg("received immediate promote request")));
> else
> ereport(LOG, (errmsg("received promote request")));

This seems fine to me.

> Or we should name the normal promotion?

No need.

> >
> > 2.
> > StartupXLOG()
> > {
> > ..
> > + if (immediate_promote)
> > + break;
> > ..
> > }
> >
> > Why are you doing this check after pause
> > (recoveryApplyDelay/recoveryPausesHere) for recovery?
> >
> > Why can't we do it after ReadRecord()?
>
> We can do that check either after ReadRecord() or after pause.
> I preferred to add the check after pause because immediate promotion
> would be likely to be requested while recovery is being paused.
> In this case, if we do that check after ReadRecord(), we need to read
> one more WAL record that actually we don't need.

Okay, but for that you need to make sure that pause can detect
promotion request.

> BTW, in the current patch, when immediate promotion is requested while
> recovery is being paused, the recovery keeps being paused until it's
> manually resumed. But immediate promotion should cause even paused
> recovery to end immediately?

Yeap, I also think so.

Another issue with immediate promotion is that currently if primary server
is continuously sending the data, then standby could not detect --immediate
promote request and the reason seems to be below code:
WaitForWALToBecomeAvailable()
{
...
{
/* just make sure source info is correct... */
readSource = XLOG_FROM_STREAM;
XLogReceiptSource = XLOG_FROM_STREAM;
return true;
}
..
if (CheckForStandbyTrigger())
}

Basically we won't check for promote request if the data is available.

I have even reproduced this by below test case:
Primary (session-1) -
1. Create table t1 (c1 int, c2 char(500)) with (fillfactor = 10);

Standby -
2. Configure and start standby
3. Just connect with one client

Primary (session-1) -
4. insert into t1 values (generate_series(1,10), 'a');

>From another window, run command:
5. pg_ctl promote --immediate -D ..\..\Database1

Run step-4 and step-5 at the same time.

Currently standby is promoted only after insert operation
in step-4 is finished which seems to be wrong.

Apart from above issue, I have one question for you regarding
this feature, currently the patch supports immediate promotion
via pg_ctl promote, however we have another mechanism (trigger_file)
which you have not enhanced to support this new feature.  Is there
any reason for same?


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: [HACKERS] [REVIEW] Re: Compression of full-page-writes

[Fujii Masao]

On Thu, Aug 28, 2014 at 12:46 AM, Arthur Silva  wrote:
>
> Em 26/08/2014 09:16, "Fujii Masao"  escreveu:
>
>
>>
>> On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed 
>> wrote:
>> > Hello,
>> > Thank you for comments.
>> >
>> >>Could you tell me where the patch for "single block in one run" is?
>> > Please find attached patch for single block compression in one run.
>>
>> Thanks! I ran the benchmark using pgbench and compared the results.
>> I'd like to share the results.
>>
>> [RESULT]
>> Amount of WAL generated during the benchmark. Unit is MB.
>>
>> MultipleSingle
>> off202.0201.5
>> on6051.06053.0
>> pglz3543.03567.0
>> lz43344.03485.0
>> snappy3354.03449.5
>>
>> Latency average during the benchmark. Unit is ms.
>>
>> MultipleSingle
>> off19.119.0
>> on55.357.3
>> pglz45.045.9
>> lz444.244.7
>> snappy43.443.3
>>
>> These results show that FPW compression is really helpful for decreasing
>> the WAL volume and improving the performance.
>>
>> The compression ratio by lz4 or snappy is better than that by pglz. But
>> it's difficult to conclude which lz4 or snappy is best, according to these
>> results.
>>
>> ISTM that compression-of-multiple-pages-at-a-time approach can compress
>> WAL more than compression-of-single-... does.
>>
>> [HOW TO BENCHMARK]
>> Create pgbench database with scall factor 1000.
>>
>> Change the data type of the column "filler" on each pgbench table
>> from CHAR(n) to TEXT, and fill the data with the result of pgcrypto's
>> gen_random_uuid() in order to avoid empty column, e.g.,
>>
>>  alter table pgbench_accounts alter column filler type text using
>> gen_random_uuid()::text
>>
>> After creating the test database, run the pgbench as follows. The
>> number of transactions executed during benchmark is almost same
>> between each benchmark because -R option is used.
>>
>>   pgbench -c 64 -j 64 -r -R 400 -T 900 -M prepared
>>
>> checkpoint_timeout is 5min, so it's expected that checkpoint was
>> executed at least two times during the benchmark.
>>
>> Regards,
>>
>> --
>> Fujii Masao
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>
> It'd be interesting to check avg cpu usage as well.

Yep, but I forgot to collect those info...

Regards,

-- 
Fujii Masao


-- 
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] [REVIEW] Re: Compression of full-page-writes

[Fujii Masao]

On Wed, Aug 27, 2014 at 11:52 PM, Robert Haas  wrote:
> On Tue, Aug 26, 2014 at 8:14 AM, Fujii Masao  wrote:
>> On Tue, Aug 19, 2014 at 6:37 PM, Rahila Syed  wrote:
>>> Hello,
>>> Thank you for comments.
>>>
Could you tell me where the patch for "single block in one run" is?
>>> Please find attached patch for single block compression in one run.
>>
>> Thanks! I ran the benchmark using pgbench and compared the results.
>> I'd like to share the results.
>>
>> [RESULT]
>> Amount of WAL generated during the benchmark. Unit is MB.
>>
>> MultipleSingle
>> off202.0201.5
>> on6051.06053.0
>> pglz3543.03567.0
>> lz43344.03485.0
>> snappy3354.03449.5
>>
>> Latency average during the benchmark. Unit is ms.
>>
>> MultipleSingle
>> off19.119.0
>> on55.357.3
>> pglz45.045.9
>> lz444.244.7
>> snappy43.443.3
>>
>> These results show that FPW compression is really helpful for decreasing
>> the WAL volume and improving the performance.
>
> Yeah, those look like good numbers.  What happens if you run it at
> full speed, without -R?

OK, I ran the same benchmark except -R option. Here are the results:

[RESULT]
Throughput in the benchmark.

MultipleSingle
off2162.62164.5
on891.8895.6
pglz1037.21042.3
lz41084.71091.8
snappy1058.41073.3

Latency average during the benchmark. Unit is ms.

MultipleSingle
off29.629.6
on71.771.5
pglz61.761.4
lz459.058.6
snappy60.559.6

Amount of WAL generated during the benchmark. Unit is MB.

MultipleSingle
off948.0948.0
on7675.57702.0
pglz5492.05528.5
lz45494.55596.0
snappy5667.05804.0

pglz vs. lz4 vs. snappy
In this benchmark, lz4 seems to have been the best compression
algorithm.
It caused best performance and highest WAL compression ratio.

Multiple vs. Single
WAL volume with "Multiple" was smaller than that with "Single". But
the throughput was better in "Single". So the "Multiple" is more useful
for WAL compression, but it may cause higher performance overhead
at least in current implementation.

Regards,

-- 
Fujii Masao


-- 
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] INSERT ... ON CONFLICT {UPDATE | IGNORE}

[Peter Geoghegan]

On Thu, Aug 28, 2014 at 8:05 PM, Peter Geoghegan  wrote:
> I realized that I missed a few cases here. For one thing, the posted
> patch fails to arrange for the UPDATE post-parse-analysis tree
> representation to go through the rewriter stage (on the theory that
> user-defined rules shouldn't be able to separately affect the
> auxiliary UPDATE query tree), but rewriting is at least necessary so
> that rewriteTargetListIU() can expand a "SET val = DEFAULT"
> targetlist, as well as normalize the ordering of the UPDATE's tlist.
> Separately, the patch fails to defend against certain queries that
> ought to be disallowed, where a subselect is specified with a subquery
> expression in the auxiliary UPDATE's WHERE clause.

Attached revision fixes all of these issues. I've added regression
tests for each bug, too, although all changes are rebased into my
original commits.

I decided to explicitly rely on a simpler approach to VACUUM
interlocking. I no longer bother holding on to a buffer pin for a
period longer than the period that associated "value locks" are held,
which was something I talked about at the start of this thread. There
is a note on this added to the nbtree README, just after the master
branch's current remarks on B-Tree VACUUM interlocking.

I've also pushed the responsibility for supporting this new feature on
foreign tables onto FDWs themselves. The only writable FDW we
currently ship, postgres_fdw, lacks support for the new feature, but
this can be revisited in due course. My impression is that the task of
adding support is not quite a straightforward matter of adding a bit
more deparsing logic, but also isn't significantly more difficult than
that.

-- 
Peter Geoghegan


0001-Make-UPDATE-privileges-distinct-from-INSERT-privileg.patch.gz
Description: GNU Zip compressed data


0004-Internal-documentation-for-INSERT-.-ON-CONFLICT-UPDA.patch.gz
Description: GNU Zip compressed data


0003-Tests-for-INSERT-.-ON-CONFLICT-UPDATE-IGNORE.patch.gz
Description: GNU Zip compressed data


0002-Support-INSERT-.-ON-CONFLICT-UPDATE-IGNORE.patch.gz
Description: GNU Zip compressed 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] PL/pgSQL 2

[Heikki Linnakangas]

On 09/02/2014 09:06 AM, Joel Jacobson wrote:

Given the needed diff between plpgsql and plpgsql2 for the changes I'm
mostly interested in would probably be quite small,
I'm in favour of Tom's suggestion of:

c) plpgsql and plpgsql2 are the same code base, with a small number
of places that act differently depending on the language version.


That fits perfectly for my needs, as I don't want to change much.

But even if we find we want to make larger mostly-compatible changes,
maybe that also can be implemented using the same approach.

For me, the most important is to not break *most* of existing plpgsql
code, but it's OK to break *some*.
And when breaking it, it should be trivial to rewrite it to become compatible.


I think the next step would be to list all the things you don't like 
with current PL/pgSQL, and write down how you would want them to work if 
you were starting with a clean slate. Let's see how wide the consensus 
is that the new syntax/behavior is better than what we have now. We can 
then start thinking how to best adapt them to the current PL/pgSQL 
syntax and codebase. Maybe with pragmas, or new commands, or deprecating 
the old behavior; the best approach depends on the details, and how 
widely desired the new behavior is, so we need to see that first.


I'd suggest collecting the ideas on a wiki page, and once you have some 
concrete set of features and syntax there, start a new thread to discuss 
them. Others will probably have other features they want, like the 
simpler "DROP TABLE ?" thing.


- Heikki



--
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] PL/pgSQL 2

[Joel Jacobson]

On Tue, Sep 2, 2014 at 5:46 AM, Craig Ringer  wrote:
> My point is that weeks can be spent just arguing about whether you
> should have a variable-delimiter ($variable) or not, how syntax should
> look, etc. Imagine how long it'd take to get a new language syntax
> agreed upon?

I would guess about a year.

> You jumped in to say that you thought that:
>
>   EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>
> was "is exactly why we need a new language" and that "All the clumsy
> stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with
> the most beautiful syntax we can come up with." But you haven't said HOW
> you propose to fix this one case.
>
> Show me. How do you want this to look? The user requirement is "Execute
> a SELECT against a table whose name is provided at runtime, selecting a
> column or set of columns whose names are provided at runtime, with
> literals substituted as placement parameters."
>
> The above is ugly. Fine, not arguing. Show me what you want instead.
>
>
> You're happy to say how much you dislike PL/PgSQL, but I haven't seen a
> concrete proposal on how you want something new to look. That would be a
> useful and constructive start, as we could then examine, point-by-point,
> how/if those needs can be met in PL/PgSQL. If they can't then you'd have
> a more convincing argument for a new version than "PL/PgSQL sucks".

I've *never* said PL/pgSQL sucks.
I *love* PL/pgSQL, seriously.
I write code for many hours a day in the language.
I don't even want to change much.
My wishlist consists mostly of things which makes the language more secure.
Currently it's a pain to verify your data operations do exactly what
you requested.
I would guess most novice developers don't understand this, and by
mistake write insecure code.
I don't want any OO. I don't want PL/SQL or PL/PSM. I'm a happy camper
with PL/pgSQL.

That said, *if* we now have a one-shot opportunity of possibly
breaking a bit of compatibility for a minority of current code,
motivated by the introduction of new important features not possible
without plpgsql2, *then* let's make the best of that opportunity.

I don't find myself selecting from a table which table name I don't
know the name when writing the code,
so I'm not pariticulary interested in prodiving a syntax for that use case,
but I'm not against the feature if others need it, even if it would
possibly increase the lines of code of existing plpgsql code which
needs to be modified to remain compatible by X %.

Given the needed diff between plpgsql and plpgsql2 for the changes I'm
mostly interested in would probably be quite small,
I'm in favour of Tom's suggestion of:
>c) plpgsql and plpgsql2 are the same code base, with a small number
>of places that act differently depending on the language version.

That fits perfectly for my needs, as I don't want to change much.

But even if we find we want to make larger mostly-compatible changes,
maybe that also can be implemented using the same approach.

For me, the most important is to not break *most* of existing plpgsql
code, but it's OK to break *some*.
And when breaking it, it should be trivial to rewrite it to become compatible.


-- 
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 for psql History Display on MacOSX

[Tom Lane]

Noah Misch  writes:
> On Mon, Sep 01, 2014 at 10:22:57PM -0400, Tom Lane wrote:
>> Also, as best I can tell, .psql_history files from older libedit versions
>> are not forward-compatible to current libedit versions because of the
>> failure of the decode_history() loop to reach all lines of the file
>> when using current libedit.  That is also a back-patchable bug fix IMO.
>> (Closer investigation suggests this is a bug or definitional change in
>> libedit's history_set_pos, not so much in next_history vs
>> previous_history.  But whatever it is, it behooves us to work around it.)

> I haven't studied this part of the topic other than to read what you have
> written.  All other things being equal, I agree.  If fixing this will make
> psql-9.3.6 w/ libedit-20141001 write history files that confuse psql-9.3.5 w/
> libedit-20141001, that changes the calculus.  Will it?

I'm not sure exactly when things changed, but I have verified that the
existing loops in decode/encode_history visit all lines of the history
when using OS X Tiger's libedit library.  On OS X Mavericks, the loops
visit only the oldest history entry, as Stepan reported.  This means that
there may be libedit-style ~/.psql_history files out there in which ^A has
been substituted for ^J (in lines after the oldest), which will not be
correctly reloaded by psql versions using newer libedit.

It's certainly arguable whether this is an issue warranting a back-patch,
since we've not heard field complaints about it AFAIR.  But I think we
ought to do so.  I think "psql N produces files that psql N+1 can't read"
is worse than the reverse case, and that's exactly what we're debating
here.

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] Patch for psql History Display on MacOSX

[Noah Misch]

On Mon, Sep 01, 2014 at 10:22:57PM -0400, Tom Lane wrote:
> Noah Misch  writes:
> > On Mon, Sep 01, 2014 at 02:05:37PM -0400, Tom Lane wrote:
> >> Functionally this seems like a clear win over what we had, especially
> >> since it supports using the pager.  I'm inclined to think we should
> >> not only apply this change but back-patch it.
> 
> > I've not used \s apart from verifying that certain patches didn't break it.
> > (That "less ~/.psql_history" beats dumping thousands of lines to the tty 
> > was a
> > factor.)  "\s fname" is theoretically useful as an OS-independent 
> > alternative
> > to "cp ~/.psql_history fname".  I see too little certainty of net benefit to
> > justify a minor-release change to this.
> 
> I disagree.  \s to the tty is *completely broken* on all but quite old
> libedit releases, cf
> http://www.postgresql.org/message-id/17435.1408719...@sss.pgh.pa.us
> That seems to me to be a bug worthy of back-patching a fix for.

I'm with you that far.  Given a patch that does not change "\s /tmp/foo" and
that makes "\s" equivalent to "\s /tmp/foo" + "\! cat /tmp/foo >/dev/tty",
back-patch by all means.  No patch posted on this thread is so surgical, hence
my objection.  In particular, your latest patch revision changes "\s /tmp/foo"
to match the novel output the patch introduces for plain "\s".  "\s /tmp/foo"
would no longer write data that libedit can reload as a history file.  I'm
cautiously optimistic that nobody relies on today's "\s /tmp/foo" behavior,
but I'm confident that folks can wait for 9.5 to get the envisaged benefits.

> Also, as best I can tell, .psql_history files from older libedit versions
> are not forward-compatible to current libedit versions because of the
> failure of the decode_history() loop to reach all lines of the file
> when using current libedit.  That is also a back-patchable bug fix IMO.
> (Closer investigation suggests this is a bug or definitional change in
> libedit's history_set_pos, not so much in next_history vs
> previous_history.  But whatever it is, it behooves us to work around it.)

I haven't studied this part of the topic other than to read what you have
written.  All other things being equal, I agree.  If fixing this will make
psql-9.3.6 w/ libedit-20141001 write history files that confuse psql-9.3.5 w/
libedit-20141001, that changes the calculus.  Will it?

> You could certainly argue that the introduction of pager support is a
> feature addition not a bug fix, but I can't really see the point of
> leaving out that part of the patch in the back branches.  The lack of
> pager support in \s has been an acknowledged bug since forever, and I
> don't think the pager calls in the new code are the riskiest part of it.

Agreed; if the pager support were the only debatable aspect, I would not have
commented.


-- 
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] inherit support for foreign tables

[Kyotaro HORIGUCHI]

Hello, I have a request with slight significance for the messages.

> I'd like to address this by emitting the second message as shown below:
> 
> INFO:  analyzing "public.parent"
> INFO:  "parent": scanned 0 of 0 pages, containing 0 live rows and 0 dead 
> rows; 0 rows in sample, 0 estimated total rows
> INFO:  analyzing "public.parent" inheritance tree
> INFO:  skipping analyze of "public.parent" inheritance tree --- this 
> inheritance tree contains foreign tables

In acquire_inherited_sample_rows(), the message below is emitted
when the parent explicitly specified in analyze command has at
least one foreign tables.

>"skipping analyze of \"%s.%s\" inheritance tree --- this
> inheritance tree contains foreign tables"

This message implicitly asserts (for me) that "A inheritance tree
containing at least one foreign tables *always* cannot be
analyzed" but in reality, we can let it go by specifying the
parent table explicitly. For example, the additional HINT or
DETAIL message would clarify that.

> INFO:  analyzing "public.parent" inheritance tree
> INFO:  skipping analyze of "public.parent" inheritance tree --- this 
> inheritance tree contains foreign tables
+ HINT:  You can analyze this inheritance tree by specifying "public.parent" to 
analze command

-- 
Kyotaro Horiguchi
NTT Open Source Software Center


-- 
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] On partitioning

[Craig Ringer]

On 09/01/2014 04:03 AM, Tom Lane wrote:

> I think one of the key design decisions we have to make is whether
> partitions are all constrained to have exactly the same set of indexes.

... and a lot of that comes down to what use cases the partitioning is
meant to handle, and what people are expected to continue to DIY with
inheritance.

Simple range and hash partitioning are the main things being discussed.

Other moderately common partitioning uses seem to be hot/cold
partitioning, usually on unequal ranges, and closely related live/dead
partitioning for apps that soft-delete data.

In both those you may well want to suppress indexes on the cold/dead
portion, much like we currently have partial indexes.

In fact, how different is an index that's present on only a subset of
partitions to a partial index, in planning terms? We know the partitions
it is/isn't on, after all, and can form an expression that finds just
those partitions.

(I guess the answer there is that partial index planning is probably not
smart enough to be useful for this).

> If we don't insist on that it will greatly complicate planning compared
> to what we'll get if we do insist on it, because then the planner will
> need to generate a separate customized plan subtree for each partition.

Seems to be like a "make room to support it in future, but don't do it
now" thing.

Partitioning schemes like:

[prior years]
[last year]
[this year]
[this month]
[this week]

could benefit from it, but they also need things like online
repartitioning, updates to move tuples across partitions, etc.

So it's all in the "let's not lock it out for the future, but lets not
tackle it now either" box.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] On partitioning

[Craig Ringer]

On 09/01/2014 11:52 PM, Andres Freund wrote:
> I personally think that implementing cross partition indexes has a low
> enough cost/benefit ratio that I doubt it's wise to tackle it anytime
> soon.

UNIQUE constraints on partitioned tables (and thus foreign key
constraints pointing to partitioned tables) are a pretty big limitation
at the moment.

That said, the planner may well be able to use the greater knowledge of
the partitioned table structure to do this implictly, as it knows that a
unique index on the partition is also implicitly unique across
partitions on the partitioning key.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Craig Ringer]

On 09/02/2014 12:40 PM, Tom Lane wrote:
> As a case in point, EDB have spent quite a few man-years on their Oracle
> compatibility layer; and it's still not a terribly exact match, according
> to my colleagues who have looked at it.  So that is a tarbaby I don't
> personally care to touch ... even ignoring the fact that cutting off
> EDB's air supply wouldn't be a good thing for the community to do.

Yep. Especially as PL/SQL is not a lovely language to work with anyway;
if the goal was "a better built-in PL" then PL/SQL wouldn't be my first
choice by any stretch.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Tom Lane]

Craig Ringer  writes:
> If someone came up with a convincing PL/SQL compatibility layer then
> it'd be worth considering adopting - when it was ready. But of course,
> anyone who does the work for that is quite likely to want to sell it to
> cashed-up Oracle users looking to save a few hundred grand on per-CPU
> licensing.

As a case in point, EDB have spent quite a few man-years on their Oracle
compatibility layer; and it's still not a terribly exact match, according
to my colleagues who have looked at it.  So that is a tarbaby I don't
personally care to touch ... even ignoring the fact that cutting off
EDB's air supply wouldn't be a good thing for the community to do.

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] orangutan seizes up during isolation-check

[Tom Lane]

Noah Misch  writes:
> Buildfarm member orangutan has failed chronically on both of the branches for
> which it still reports, HEAD and REL9_1_STABLE, for over two years.  The
> postmaster appears to jam during isolation-check.  Dave, orangutan currently
> has one such jammed postmaster for each branch.  Could you gather some
> information about the running processes?

What's particularly odd is that orangutan seems to be running an only
slightly out-of-date OS X release, which is hardly an unusual
configuration.  My own laptop gets through isolation-check just fine.
Seems like there must be something nonstandard about orangutan's
software ... but what?

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] PL/pgSQL 2

[Ian Barwick]

On 14/09/02 12:24, Craig Ringer wrote:
> On 09/02/2014 08:09 AM, Neil Tiffin wrote:
(...)

>> That should be enough alone to suggest postgreSQL start working on a modern, 
>> in core, fast, fully supported language.
> 
> I couldn't disagree more.
> 
> If we were to implement anything, it'd be PL/PSM
> (http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
> quirky as anything else the SQL committee has brought forth, but it's at
> least a standard(ish) language.

For reference, and without wading into the general debate, there is an
existing, albeit outdated and dormant PL/PSM implementation:

  http://pgfoundry.org/frs/?group_id=1000238
  http://postgres.cz/wiki/SQL/PSM_Manual

>From my (limited) experience with the MySQL variant, it makes PL/pgSQL
look positively concise and elegant. Though that's just my subjective
opinion  (possibly coloured by the particular implementation) and not
necessarily a pro/contra argument ;).


Regards

Ian Barwick

-- 
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Mark Kirkwood]

On 02/09/14 15:46, Craig Ringer wrote:


was "is exactly why we need a new language" and that "All the clumsy
stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with
the most beautiful syntax we can come up with." But you haven't said HOW
you propose to fix this one case.


Unfortunately, there is likely to be a (large) variance of opinion 
concerning the details. In particular 'beautiful/elegant...'. Err - 
these things are mostly in the eye of the beholder. E.g: I might want 
this new shiny syntax to be lisp like, as that is beautiful (heh, 
kidding - but you should get the idea).


Cheers

Mark


--
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] Optimization for updating foreign tables in Postgres FDW

[Shigeru Hanada]

I confirmed performance gain accomplished by this patch.

This patch makes update queries ~50x faster, and even hit-miss update
is 3x faster than original.  Of course benefit is only for queries
whose qualifiers are enough simple so that they can be pushied down
fully, but this improvement is remarkable.

This patch avoids 1) SELECT for determining target rows, and 2)
repeated per-row UPDATE/DELETE in particular situation, so I assumed
that the gain is larger for bulk update, and it's true indeed, but in
fact even hit-miss update (0 row affected) become faster enough.  This
would come from the omission of SELECT preceding repeated
UPDATE/DELETE.

I was little worried about overhead in planning phase, but fluctuation
was less than 1ms, so it's negligible.

Measurement Result
==

Note: numbers below are "execution time" of EXPLAIN ANALYZE, and
average of five runs
--+-+---+
rows affected |original |   patched |   gain
--+-+---+
0 |   4.841 | 1.548 |  3.13x
1 |   6.944 | 1.793 |  3.87x
  100 | 174.420 | 5.167 | 33.76x
   10,000 |   8,215.551 |   163.832 | 50.15x
  100,000 |  78,135.905 | 1,595.739 | 48.97x
  200,000 | 179,784.928 | 4,305.856 | 41.75x
--+-+---+

Measurement procedure
=

[Local side]
1) Create foreign table which refers pgbench_accounts on the remote side

[Remote side]
2) pgbench -i -s 100
3) Execute ANALYZE
4) Restart PostgreSQL to clear shared buffers

[Local side]
5) Execute ANALYZE against foreign table
6) Execute UPDATE SQL against foreign table once for warm the cache
7) Execute UPDATE SQL against foreign table five times

Test SQL for 1-rows cas is below, only aid condition is changed
according to measurement variation.

EXPLAIN ANALYZE VERBOSE UPDATE ft_pgbench_accounts SET bid=bid+1,
abalance=abalance+1, filler='update test' WHERE aid<=1;

2014-08-29 12:59 GMT+09:00 Etsuro Fujita :
> (2014/08/26 12:20), Etsuro Fujita wrote:
>>
>> (2014/08/25 21:58), Albe Laurenz wrote:
>>>
>>> I played with it, and apart from Hanada's comments I have found the
>>> following:
>>>
>>> test=> EXPLAIN (ANALYZE, VERBOSE) UPDATE rtest SET val=NULL WHERE id > 3;
>>>  QUERY PLAN
>>>
>>> --
>>>
>>>   Update on laurenz.rtest  (cost=100.00..14134.40 rows=299970
>>> width=10) (actual time=0.005..0.005 rows=0 loops=1)
>>> ->  Foreign Scan on laurenz.rtest  (cost=100.00..14134.40
>>> rows=299970 width=10) (actual time=0.002..0.002 rows=27 loops=1)
>>>   Output: id, val, ctid
>>>   Remote SQL: UPDATE laurenz.test SET val = NULL::text WHERE
>>> ((id > 3))
>>>   Planning time: 0.179 ms
>>>   Execution time: 3706.919 ms
>>> (6 rows)
>>>
>>> Time: 3708.272 ms
>>>
>>> The "actual time" readings are surprising.
>>> Shouldn't these similar to the actual execution time, since most of
>>> the time is spent
>>> in the foreign scan node?
>>
>>
>> I was also thinkng that this is confusing to the users.  I think this is
>> because the patch executes the UPDATE/DELETE statement during
>> postgresBeginForeignScan, not postgresIterateForeignScan, as you
>> mentioned below:
>>
>>> Reading the code, I noticed that the pushed down UPDATE or DELETE
>>> statement is executed
>>> during postgresBeginForeignScan rather than during
>>> postgresIterateForeignScan.
>
>
>> I'll modify the patch so as to execute the statement during
>> postgresIterateForeignScan.
>
>
> Done.
>
>
>>> It is not expected that postgresReScanForeignScan is called when the
>>> UPDATE/DELETE
>>> is pushed down, right?  Maybe it would make sense to add an assertion
>>> for that.
>>
>>
>> IIUC, that is right.  As ModifyTable doesn't support rescan currently,
>> postgresReScanForeignScan needn't to be called in the update pushdown
>> case.  The assertion is a good idea.  I'll add it.
>
>
> Done.
>
> You can find the updated version of the patch at
>
> http://www.postgresql.org/message-id/53fffa50.6020...@lab.ntt.co.jp
>
>
> Thanks,
>
> Best regards,
> Etsuro Fujita



-- 
Shigeru HANADA


-- 
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] Concurrently option for reindexdb

[Craig Ringer]

On 09/02/2014 11:10 AM, Sawada Masahiko wrote:
> The currently patch dose not hack catalog, just create new index
> concurrently and
> swap them.
> So, It is supporting only UNIQUE index, I think.

UNIQUE indexes, but not a UNIQUE constraint backed by a UNIQUE index, or
a PRIMARY KEY constraint backed by a UNIQUE index.

> This patch contains some limitation.
> Also I'm thinking to implement to handle these cases.

My understanding from the prior discussion is that any satisfactory
solution to those problems would also make it possible to support
REINDEX CONCURRENTLY natively.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Craig Ringer]

On 09/02/2014 11:42 AM, David Johnston wrote:
> 
> ​Yet pl/pgsql does not have to use SPI-interface type calls to interact
> with PostgreSQL at the SQL level...

That's right.

> ​I don't have an answer to your questions but the one I'm asking is
> whether a particular language could hide all of the SPI stuff behind
> some custom syntax so that it in effect looks similar to what pl/pgsql
> does today?

Sure, it's possible. Have you seen Microsoft's "LINQ" for C# / .NET? One
of the few really awesome things they've done in a long time.

Do you see many other languages with good seamless SQL support, though?
The only one I can think of off the top of my head is PROGRESS/4GL, and
like PL/PgSQL it's designed around the idea of being a
database-integrated language.

The hard problem to solve here isn't "how do I make the language talk to
the database server backend". That's easy. The hard problem is "how
should the integrated SQL support look and work so it fits in with the
language while remaining as powerful and expressive as SQL its self".

It's *the same* problem as if you were using the same language via a
network connection to the database, instead of embedding it. How do you
make SQL syntax interoperate with your language's syntax, or produce a
clean-ish adapter layer?

Lots of people have tried. Most have failed dismally. ActiveRecord
becomes gruesomely ugly hodgepodge of text snippets and code as soon as
you try to do anything interesting/nontrivial with it. Hibernate
Criteria Query - ever wanted to write 100 lines of code for a simple
query? You'll love it. JOOQ? Django ORM? SQLAlchemy? iBatis/MyBatis?
QueryDSL? They're all ugly in various ways.

The underlying problem is that SQL, syntactically speaking, is a weird,
clunky way to do things that sticks out like a sore thumb when you put
it together with a language designed after 1975.

Also, most of the "modern" languages people want to use are to some
degree OO, not just procedural. That brings the OO/relational mismatch
into play, and there's no pretty and easy solution for that.

http://en.wikipedia.org/wiki/Object-relational_impedance_mismatch

PL/PgSQL gets away with it by not being OO - it fits its model around
that of SQL.

If you can solve that problem - create a smooth, seamless
language-integrated way to use SQL in any language of your choice - then
please, please come talk about it here.

> Or, more to the point, does pl/pgsql use the same SPI
> interface behind the scenes as PL/Perl or does it have its own special
> interface?

Take a look.

cd src/pl/pgsql/src

You'll see pl_handler.c, the PL/PgSQL procedural language handler.
There's also pl_exec.c, containing the guts of the language runtime. SPI
use is heavy throughout.

PL/PgSQL uses SPI to execute queries and fetch results. See, for
example, exec_stmt_execsql(...) in pl_exec.c .

Any other language that can run embedded into the PostgreSQL backend can
do the same.

PL/Python does it:

http://www.postgresql.org/docs/current/static/plpython-database.html

(though unfortunately the PL/Python interface for SQL does't follow the
Python DB-API).

PL/V8, PL/Lua, PL/Ruby, they all use the SPI. Same as PL/PgSQL.

The challenge isn't dispatching the SQL, the challenge is fitting SQL
into the language seamlessly.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Craig Ringer]

On 09/01/2014 11:19 PM, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer  wrote:
>> On 09/01/2014 10:41 PM, Joel Jacobson wrote:
>>> This is exactly why we need a new language.
>>> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
>>> plpgsql2, with the most beautiful syntax we can come up with.
>>>
>>> I guess it's a question if we want to support things like this. If we
>>> want to, then we also want a new language.
>>
>> Given how much bike shedding occurs around trivial features, can you
>> imagine how long that'd take?
> 
> I wasn't aware of the expression "bike shedding" so I had to look it up.
> It apparently means "spend the majority of its time on relatively
> unimportant but easy-to-grasp issues".
> If you feel the development of plpgsql falls into this category, that
> most time is spent on the smaller unimportant things, isn't that a
> clear sign we need plpgsql2, for there to be any hope of progress on
> the important things?

Er, no.

My point is that weeks can be spent just arguing about whether you
should have a variable-delimiter ($variable) or not, how syntax should
look, etc. Imagine how long it'd take to get a new language syntax
agreed upon?

You jumped in to say that you thought that:

  EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;

was "is exactly why we need a new language" and that "All the clumsy
stuff we cannot fix in plpgsql, can easily be fixed in plpgsql2, with
the most beautiful syntax we can come up with." But you haven't said HOW
you propose to fix this one case.

Show me. How do you want this to look? The user requirement is "Execute
a SELECT against a table whose name is provided at runtime, selecting a
column or set of columns whose names are provided at runtime, with
literals substituted as placement parameters."

The above is ugly. Fine, not arguing. Show me what you want instead.


You're happy to say how much you dislike PL/PgSQL, but I haven't seen a
concrete proposal on how you want something new to look. That would be a
useful and constructive start, as we could then examine, point-by-point,
how/if those needs can be met in PL/PgSQL. If they can't then you'd have
a more convincing argument for a new version than "PL/PgSQL sucks".

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[David Johnston]

On Mon, Sep 1, 2014 at 11:12 PM, Craig Ringer  wrote:

> On 09/02/2014 09:40 AM, David G Johnston wrote:
> > Random thought as I wrote that: how about considering how pl/pgsql
> > functionality can be generalize so that it is a database API that
> > another language can call?  In that way the server would drive the core
> > functionality and the language would simply be an interpreter that
> > enforces its specific notion of acceptable syntax.
>
> That's pretty much what we already have with the SPI and procedural
> language handler infrastructure. PL/Perl, PL/Python, etc exist because
> we have this.
>
> What do you see as missing from the current infrastructure? What can't
> be done that should be able to be done in those languages?
>
>
​Yet pl/pgsql does not have to use SPI-interface type calls to interact
with PostgreSQL at the SQL level...

​I don't have an answer to your questions but the one I'm asking is whether
a particular language could hide all of the SPI stuff behind some custom
syntax so that it in effect looks similar to what pl/pgsql does today?  Or,
more to the point, does pl/pgsql use the same SPI interface behind the
scenes as PL/Perl or does it have its own special interface?

David J.

Re: [HACKERS] PL/pgSQL 2

[Craig Ringer]

On 09/02/2014 05:46 AM, David G Johnston wrote:
> The goal of adding PL/SQL would be to increase the user base of the project
> and hopefully attract new blood to the development team in order to maximize
> long-term survivability and increase the pace of innovation.  We would be
> unable to introduce substantial evolution to this language for that very
> reason and so a different language is likely to be needed - eventually.

Sure - but it can be done out of core, with core patches only as
required for making things that aren't currently possible work.

You'd need to solve some really hard problems though. Autonomous
transactions and top-level true "procedures" being the big ones. You'd
also need to handle multiple result sets, TABLE-typed variables (backed
with a tuplestore, maybe?), and lots more.

Most of the differences between PL/SQL and PL/PgSQL stem from underlying
differences in PostgresSQL and Oracle, as Tom has already pointed out.
Often PostgreSQL limitations that don't exist in Oracle. So you'd have a
lot of work to do in core to make a usefully-better-than-PL/PgSQL PL/SQL
implementation possible.

If someone came up with a convincing PL/SQL compatibility layer then
it'd be worth considering adopting - when it was ready. But of course,
anyone who does the work for that is quite likely to want to sell it to
cashed-up Oracle users looking to save a few hundred grand on per-CPU
licensing.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] add line number as prompt option to psql

[Sawada Masahiko]

On Tue, Sep 2, 2014 at 11:12 AM, Andres Freund  wrote:
> On 2014-08-31 12:06:31 +0900, Sawada Masahiko wrote:
>> Thank you for review comment and improving the patch!
>> I tested it.
>> Your patch always increment line number even if there is no input line
>> as follows.
>>
>> postgres[1]=#
>> postgres[2]=# select
>> postgres[3]-# ,
>> postgres[4]-# from
>> postgres[5]-# hoge;
>> ERROR:  syntax error at or near "," at character 8
>> STATEMENT:  select
>> ,
>> from
>> hoge;
>> ERROR:  syntax error at or near ","
>> LINE 2: ,
>> ^
>> Actually error syntax is in line 2 as postgres reported.
>> But it is inconsistent.
>
> Hm. Right. That's clearly wrong.
>
>> Attached patch is resolve above behavior based on your version patch.
>
> I've looked a bit further and found two more broken things.
>
> 1)
> postgres[1]=# SELECT 1; SELECT 2
> postgres[1]=#
>
> Note the 1 in the second line. Obviously wrong.
>
> The fix for this is easy: Don't count a newline if there isn't one. But
> check for PSCAN_EOL. That also gets rid of inconsistent pset.stmt_lineno
> initializations (sometimes to 0, sometimes to 1).
>
> 2)
> postgres[1]=# SELECT 1,
> postgres[2]-# 2,
> postgres[3]-# 3;
> ┌──┬──┬──┐
> │ ?column? │ ?column? │ ?column? │
> ├──┼──┼──┤
> │1 │2 │3 │
> └──┴──┴──┘
> (1 row)
>
> postgres[1]=# SELECT 1,
> 2,
> 3;
> ┌──┬──┬──┐
> │ ?column? │ ?column? │ ?column? │
> ├──┼──┼──┤
> │1 │2 │3 │
> └──┴──┴──┘
> (1 row)
>
> postgres[3]=#
>
> Obviously the three in the last line is wrong.
>
> The fix is slightly nontrivial. It's wrong to look at 'line' when
> determining the number of lines to add - it may already be
> executed. The, it seems to me, correct thing is to look at the data
> that's appended to the query buffer. Alternatively we could always count
> all lines in the query buffer, but that'd be O(lines^2)...
>
>
> I've done both in the appended patch.
>
>
> I've now used up a perfectly good glass of wine for this, so this is it
> for today ;)
>

Thank you for updating the patch!
I tested it.
These fix looks good to me :)

Regards,

---
Sawada Masahiko

-- 
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] PL/pgSQL 2

[Craig Ringer]

On 09/02/2014 08:09 AM, Neil Tiffin wrote:
> Now I could use other languages as was suggested upstream.  Lets see, I use R 
> all the time, but R is not a first class language, not in core, and its slow. 
> Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and 
> don’t want to learn as no one else seems to use it (in my world anyway).  
> perl is the only possibility left and again, no one in my world is using Perl 
> and it’s not clear if there is a performance penalty.  The docs say the best 
> language for performance is PL/pgSQL after pure SQL.

PL/Perl is plenty fast, FWIW.

I agree that it is unfortunate that we don't have an in-core trusted
"real language" PL other than PL/Perl. I am personally hoping that PL/V8
will be in a position to be adopted as "PL/JavaScript" soon, as that
would be an excellent fit with how the language fashion world is
currently moving - JSON and JavaScript abound.

More seriously, JavaScript is also a good fit for a trusted PL. I've
long favoured Lua because of the excellent embeddable runtime and
security-friendly design, but it's never really got the uptake required
to make it a serious contender.

I'd be quite happy to see PL/JavaScript in-core.

(The other obvious candidate would be PL/Ruby, but it doesn't have an
untrusted variant, and AFAIK Ruby is no better than Python when it comes
to supporting a secure runtime: hopeless.)

> That should be enough alone to suggest postgreSQL start working on a modern, 
> in core, fast, fully supported language.

I couldn't disagree more.

If we were to implement anything, it'd be PL/PSM
(http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
quirky as anything else the SQL committee has brought forth, but it's at
least a standard(ish) language.

Creating a new language when there are already many existing contenders
is absolutely nonsensical. Other than PL/PSM the only thing that'd make
any sense would be to *pick a suitable existing language* like Lua or
JavaScript and bless it as a supported, always-available, in-core
language runtime that's compiled in by default.

> Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody 
> likes today.  Everything can be done in assembler, but no one suggests that 
> today.  Today, it is all about programmer productivity.  PL/pgSQL has a lot 
> of unnecessary stuff that sucks the life out of programmer productivity.  And 
> this should be very much a concern of the professionals that support 
> PostgreSQL

PL/PgSQL is how it is in part because of PL/SQL
(http://en.wikipedia.org/wiki/PL/SQL) which in turn owes its heritage to
Ada and Pascal.

It serves an important role. I'm not going to pretend it's pretty, but

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Craig Ringer]

On 09/02/2014 09:40 AM, David G Johnston wrote:
> Random thought as I wrote that: how about considering how pl/pgsql
> functionality can be generalize so that it is a database API that
> another language can call?  In that way the server would drive the core
> functionality and the language would simply be an interpreter that
> enforces its specific notion of acceptable syntax.

That's pretty much what we already have with the SPI and procedural
language handler infrastructure. PL/Perl, PL/Python, etc exist because
we have this.

What do you see as missing from the current infrastructure? What can't
be done that should be able to be done in those languages?

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] Concurrently option for reindexdb

[Sawada Masahiko]

On Mon, Sep 1, 2014 at 10:43 PM, Craig Ringer  wrote:
> On 08/25/2014 02:36 PM, Sawada Masahiko wrote:
>> Hi all,
>>
>> Attached WIP patch adds "-C (--concurrently)" option for reindexdb
>> command for concurrently reindexing.
>> If we specify "-C" option with any table then reindexdb do reindexing
>> concurrently with minimum lock necessary.
>> Note that we cannot use '-s' option (for system catalog) and '-C'
>> option at the same time.
>> This patch use simple method as follows.
>>
>> 1. Do "CREATE INDEX CONCURRENTLY" new index which has same definition
>> as target index
>> 2. Aquire ACCESS EXCLUSIVE LOCK to target table( and transaction starts)
>> 3. Swap old and new index
>> 4. Drop old index
>> 5. COMMIT
>
> How do you handle indexes tied to constraints - PRIMARY KEY, UNIQUE, or
> EXCLUSION constraint indexes?
>
> My understanding was that this currently required some less than lovely
> catalog hacks.
>

The currently patch dose not hack catalog, just create new index
concurrently and
swap them.
So, It is supporting only UNIQUE index, I think.
This patch contains some limitation.
Also I'm thinking to implement to handle these cases.

Regards,

---
Sawada Masahiko


-- 
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 for psql History Display on MacOSX

[Tom Lane]

I've confirmed that the attached patches work as expected in both the
oldest and newest readline and libedit versions available to me.
Barring further objections, I plan to commit and back-patch these
changes.

regards, tom lane

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 74d4618..6033dfd 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*** EOF
*** 277,283 
--no-readline


!Do not use readline for line editing and do not use the history.
 This can be useful to turn off tab expansion when cutting and pasting.


--- 277,284 
--no-readline


!Do not use Readline for line editing and do
!not use the command history.
 This can be useful to turn off tab expansion when cutting and pasting.


*** lo_import 152801
*** 2357,2368 
  \s [ filename ]
  
  
! Print or save the command line history to filename. If filename is omitted, the history
! is written to the standard output. This option is only available
! if psql is configured to use the
! GNU Readline library.
  
  

--- 2358,2370 
  \s [ filename ]
  
  
! Print psql's command line history
! to filename.
! If filename is omitted,
! the history is written to the standard output (using the pager if
! appropriate).  This command is not available
! if psql was built
! without Readline support.
  
  

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e16b4d5..e1949d8 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** exec_command(const char *cmd,
*** 1088,1107 
  		char	   *fname = psql_scan_slash_option(scan_state,
     OT_NORMAL, NULL, true);
  
- #if defined(WIN32) && !defined(__CYGWIN__)
- 
- 		/*
- 		 * XXX This does not work for all terminal environments or for output
- 		 * containing non-ASCII characters; see comments in simple_prompt().
- 		 */
- #define DEVTTY	"con"
- #else
- #define DEVTTY	"/dev/tty"
- #endif
- 
  		expand_tilde(&fname);
! 		/* This scrolls off the screen when using /dev/tty */
! 		success = saveHistory(fname ? fname : DEVTTY, -1, false, false);
  		if (success && !pset.quiet && fname)
  			printf(_("Wrote history to file \"%s\".\n"), fname);
  		if (!fname)
--- 1088,1095 
  		char	   *fname = psql_scan_slash_option(scan_state,
     OT_NORMAL, NULL, true);
  
  		expand_tilde(&fname);
! 		success = printHistory(fname, pset.popt.topt.pager);
  		if (success && !pset.quiet && fname)
  			printf(_("Wrote history to file \"%s\".\n"), fname);
  		if (!fname)
diff --git a/src/bin/psql/input.c b/src/bin/psql/input.c
index aa32a3f..2e01eb1 100644
*** a/src/bin/psql/input.c
--- b/src/bin/psql/input.c
***
*** 11,16 
--- 11,17 
  #include 
  #endif
  #include 
+ #include 
  
  #include "input.h"
  #include "settings.h"
*** initializeInput(int flags)
*** 319,340 
  
  
  /*
!  * This function saves the readline history when user
!  * runs \s command or when psql exits.
   *
   * fname: pathname of history file.  (Should really be "const char *",
   * but some ancient versions of readline omit the const-decoration.)
   *
   * max_lines: if >= 0, limit history file to that many entries.
-  *
-  * appendFlag: if true, try to append just our new lines to the file.
-  * If false, write the whole available history.
-  *
-  * encodeFlag: whether to encode \n as \x01.  For \s calls we don't wish
-  * to do that, but must do so when saving the final history file.
   */
! bool
! saveHistory(char *fname, int max_lines, bool appendFlag, bool encodeFlag)
  {
  #ifdef USE_READLINE
  
--- 320,334 
  
  
  /*
!  * This function saves the readline history when psql exits.
   *
   * fname: pathname of history file.  (Should really be "const char *",
   * but some ancient versions of readline omit the const-decoration.)
   *
   * max_lines: if >= 0, limit history file to that many entries.
   */
! static bool
! saveHistory(char *fname, int max_lines)
  {
  #ifdef USE_READLINE
  
*** saveHistory(char *fname, int max_lines, 
*** 344,354 
  	 * where write_history will fail because it tries to chmod the target
  	 * file.
  	 */
! 	if (useHistory && fname &&
! 		strcmp(fname, DEVNULL) != 0)
  	{
! 		if (encodeFlag)
! 			encode_history();
  
  		/*
  		 * On newer versions of libreadline, truncate the history file as
--- 338,352 
  	 * where write_history will fail because it tries to chmod the target
  	 * file.
  	 */
! 	if (strcmp(fname, DEVNULL) != 0)
  	{
! 		/*
! 		 * Encode \n, since otherwise readline will reload multiline history
! 		 * entries as separate lines.  (libedit doesn't really need th

Re: [HACKERS] Tips/advice for implementing integrated RESTful HTTP API

[Dobes Vandermeer]

On Mon, Sep 1, 2014 at 7:00 PM, Craig Ringer  wrote:

> On 09/02/2014 12:50 AM, Dobes Vandermeer wrote:
> > Hmm yes I am learning that the BG worker system isn't as helpful as I
> > had hoped due to the single database restriction.
> >
> > As for a writing a frontend this might be the best solution.
> >
> > A java frontend would be easy but pointless because the whole point here
> > is to provide a lightweight access method to the database for
> > environments that don't have the ability to use the jdbc or libpq
> > libraries.  Deploying a java setup would be too much trouble.
>
> If you can't run libpq, you can't run *anything* really, it's very
> lightweight. I think you misunderstood what I was saying; I'm talking
> about it acting as a proxy for HTTP-based requests, running on or in
> front of the PostgreSQL server like a server-side connection pool would.
>

I was just referring to an environment that doesn't have a binding to libpq
or JSBC, for example node.js for a long time had no postgresql client so I
didn't use PostgreSQL when I used node.js.


> Same idea as PgBouncer or PgPool. The advantage over hacking
> PgBouncer/PgPool for the job is that Tomcat can already do a lot of what
> you want using built-in, pre-existing functionality. Connection pool
> management, low level REST-style HTTP processing, JSON handling etc are
> all done for you.
>

Yeah, those are nice conveniences but I still think installing Java and
getting something to run on startup is a bit more of a hurdle.  Better maek
life easier up front by having a simple standalone proxy you can compile
and run with just whatever is already available on a typical AWS ubuntu
environment.


> > A C frontend using libevent would be easy enough to make and deploy for
> > this I guess.
> >
> > But... Maybe nobody really wants this thing anyway, there seem to be
> > some other options out there already.
>
> It's something I think would be interesting to have, but IMO to be
> really useful it'd need to support composing object graphs as json, a
> json query format, etc. So you can say "get me this customer with all
> their addresses and contact records" without having to issue a bunch of
> queries (round trips) or use ORM-style left-join-and-deduplicate hacks
> that waste bandwidth and are messy and annoying.
>

If the SQL outputs rows with ARRAY and JSON type columns in them then that
may be sufficient to construct whatever kind of JSON structure you want for
the query result.  I'm not sure why ORMs don't take better advantage of
this; maybe they're just too cross-DB or maybe this feature isn't as
powerful as I think it is?

PostgreSQL also allows you to query and index fields inside of a json
value, so at least initially you can get all this power without inventing
any new query language.

But later a translator could be made, like an ORM-ish thingy, that might
have less clutter than the SQL one because some shorthand could be used for
peeking inside the JSON structures.


> Close care to security and auth would also need to be taken. You don't
> want to be sending a username/password with each request; you need a
> reasonable authentication token system, request signing to prevent
> replay attacks, idempotent requests, etc.


Well, these would be needed for use cases where the DB is exposed to
untrusted parties, which has never been the case on projects I've worked
on.  I wouldn't be against these sorts of improvements if people want to
make them, but wouldn't matter much to me.  I was hoping to re-use postgres
built-in password/ident security system.

Cheers,

Dobes

Re: [HACKERS] add line number as prompt option to psql

[Andres Freund]

On 2014-09-02 11:19:09 +0900, Michael Paquier wrote:
> On Tue, Sep 2, 2014 at 11:12 AM, Andres Freund  wrote:
> > I've now used up a perfectly good glass of wine for this.
> Red or white? From where? Useful tips for hacking in this area are
> always useful.

Hah ;). Nothing special, but I rather like it: Bordeaux Superieur Cuvee,
Chateau Couronneau 2011.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] Patch for psql History Display on MacOSX

[Tom Lane]

Noah Misch  writes:
> On Mon, Sep 01, 2014 at 02:05:37PM -0400, Tom Lane wrote:
>> Functionally this seems like a clear win over what we had, especially
>> since it supports using the pager.  I'm inclined to think we should
>> not only apply this change but back-patch it.

> I've not used \s apart from verifying that certain patches didn't break it.
> (That "less ~/.psql_history" beats dumping thousands of lines to the tty was a
> factor.)  "\s fname" is theoretically useful as an OS-independent alternative
> to "cp ~/.psql_history fname".  I see too little certainty of net benefit to
> justify a minor-release change to this.

I disagree.  \s to the tty is *completely broken* on all but quite old
libedit releases, cf
http://www.postgresql.org/message-id/17435.1408719...@sss.pgh.pa.us
That seems to me to be a bug worthy of back-patching a fix for.

Also, as best I can tell, .psql_history files from older libedit versions
are not forward-compatible to current libedit versions because of the
failure of the decode_history() loop to reach all lines of the file
when using current libedit.  That is also a back-patchable bug fix IMO.
(Closer investigation suggests this is a bug or definitional change in
libedit's history_set_pos, not so much in next_history vs
previous_history.  But whatever it is, it behooves us to work around it.)

You could certainly argue that the introduction of pager support is a
feature addition not a bug fix, but I can't really see the point of
leaving out that part of the patch in the back branches.  The lack of
pager support in \s has been an acknowledged bug since forever, and I
don't think the pager calls in the new code are the riskiest part of it.

> Yikes.  It's already painful to see libedit and GNU readline trash each
> other's .psql_history files; let's not add a third format.

There's no third format involved in this patch, though we'd need one if
we stopped using the underlying libraries' read/write functions, since
both those formats suck for different reasons.  I agree that it might be
best if we did that, but designing and testing such a change seems well
beyond the scope of a back-patchable fix.

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] add line number as prompt option to psql

[Michael Paquier]

On Tue, Sep 2, 2014 at 11:12 AM, Andres Freund  wrote:
> I've now used up a perfectly good glass of wine for this.
Red or white? From where? Useful tips for hacking in this area are
always useful.
-- 
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] add line number as prompt option to psql

[Andres Freund]

On 2014-08-31 12:06:31 +0900, Sawada Masahiko wrote:
> Thank you for review comment and improving the patch!
> I tested it.
> Your patch always increment line number even if there is no input line
> as follows.
> 
> postgres[1]=#
> postgres[2]=# select
> postgres[3]-# ,
> postgres[4]-# from
> postgres[5]-# hoge;
> ERROR:  syntax error at or near "," at character 8
> STATEMENT:  select
> ,
> from
> hoge;
> ERROR:  syntax error at or near ","
> LINE 2: ,
> ^
> Actually error syntax is in line 2 as postgres reported.
> But it is inconsistent.

Hm. Right. That's clearly wrong.

> Attached patch is resolve above behavior based on your version patch.

I've looked a bit further and found two more broken things.

1)
postgres[1]=# SELECT 1; SELECT 2
postgres[1]=# 

Note the 1 in the second line. Obviously wrong.

The fix for this is easy: Don't count a newline if there isn't one. But
check for PSCAN_EOL. That also gets rid of inconsistent pset.stmt_lineno
initializations (sometimes to 0, sometimes to 1).

2)
postgres[1]=# SELECT 1,
postgres[2]-# 2,
postgres[3]-# 3;
┌──┬──┬──┐
│ ?column? │ ?column? │ ?column? │
├──┼──┼──┤
│1 │2 │3 │
└──┴──┴──┘
(1 row)

postgres[1]=# SELECT 1,
2,
3;
┌──┬──┬──┐
│ ?column? │ ?column? │ ?column? │
├──┼──┼──┤
│1 │2 │3 │
└──┴──┴──┘
(1 row)

postgres[3]=# 

Obviously the three in the last line is wrong.

The fix is slightly nontrivial. It's wrong to look at 'line' when
determining the number of lines to add - it may already be
executed. The, it seems to me, correct thing is to look at the data
that's appended to the query buffer. Alternatively we could always count
all lines in the query buffer, but that'd be O(lines^2)...


I've done both in the appended patch.


I've now used up a perfectly good glass of wine for this, so this is it
for today ;)

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
>From a4823d67181c8b5109f6d01a7a41f8dfbfdc86c5 Mon Sep 17 00:00:00 2001
From: Andres Freund 
Date: Tue, 2 Sep 2014 04:10:30 +0200
Subject: [PATCH] Add psql PROMPT variable showing which line of a statement is
 being edited.

The new %l substitution shows the line number inside a (potentially
multi-line) statement starting from one.

Author: Sawada Masahiko, heavily editorialized by me.
Reviewed-By: Jeevan Chalke, Alvaro Herrera
---
 doc/src/sgml/ref/psql-ref.sgml |  9 +
 src/bin/psql/copy.c| 15 +--
 src/bin/psql/mainloop.c| 23 +++
 src/bin/psql/prompt.c  |  5 +
 src/bin/psql/settings.h|  1 +
 5 files changed, 47 insertions(+), 6 deletions(-)

diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 74d4618..db314c3 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -3316,6 +3316,15 @@ testdb=> INSERT INTO my_table VALUES (:'content');
   
 
   
+%l
+
+ 
+  The line number inside the current statement, starting from 1.
+ 
+
+  
+
+  
 %digits
 
 
diff --git a/src/bin/psql/copy.c b/src/bin/psql/copy.c
index 4b74915..90f4a24 100644
--- a/src/bin/psql/copy.c
+++ b/src/bin/psql/copy.c
@@ -517,8 +517,8 @@ bool
 handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
 {
 	bool		OK;
-	const char *prompt;
 	char		buf[COPYBUFSIZ];
+	bool		showprompt = false;
 
 	/*
 	 * Establish longjmp destination for exiting from wait-for-input. (This is
@@ -540,21 +540,20 @@ handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
 	/* Prompt if interactive input */
 	if (isatty(fileno(copystream)))
 	{
+		showprompt = true;
 		if (!pset.quiet)
 			puts(_("Enter data to be copied followed by a newline.\n"
    "End with a backslash and a period on a line by itself."));
-		prompt = get_prompt(PROMPT_COPY);
 	}
-	else
-		prompt = NULL;
 
 	OK = true;
 
 	if (isbinary)
 	{
 		/* interactive input probably silly, but give one prompt anyway */
-		if (prompt)
+		if (showprompt)
 		{
+			const char *prompt = get_prompt(PROMPT_COPY);
 			fputs(prompt, stdout);
 			fflush(stdout);
 		}
@@ -589,8 +588,9 @@ handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
 			bool		firstload;
 			bool		linedone;
 
-			if (prompt)
+			if (showprompt)
 			{
+const char *prompt = get_prompt(PROMPT_COPY);
 fputs(prompt, stdout);
 fflush(stdout);
 			}
@@ -650,7 +650,10 @@ handleCopyIn(PGconn *conn, FILE *copystream, bool isbinary, PGresult **res)
 			}
 
 			if (copystream == pset.cur_cmd_source)
+			{
 pset.lineno++;
+pset.stmt_lineno++;
+			}
 		}
 	}
 
diff --git a/src/bin/psql/mainloop.c b/src/bin/psql/mainloop.c
index c3aff20..f07801c 100

Re: [HACKERS] Tips/advice for implementing integrated RESTful HTTP API

[Craig Ringer]

On 09/02/2014 12:50 AM, Dobes Vandermeer wrote:
> Hmm yes I am learning that the BG worker system isn't as helpful as I
> had hoped due to the single database restriction.
> 
> As for a writing a frontend this might be the best solution.
> 
> A java frontend would be easy but pointless because the whole point here
> is to provide a lightweight access method to the database for
> environments that don't have the ability to use the jdbc or libpq
> libraries.  Deploying a java setup would be too much trouble.

If you can't run libpq, you can't run *anything* really, it's very
lightweight. I think you misunderstood what I was saying; I'm talking
about it acting as a proxy for HTTP-based requests, running on or in
front of the PostgreSQL server like a server-side connection pool would.

You can have:

[client machine][PostgreSQL server machine   ]
client --- HTTP --- Tomcat/JAX-RS  -- pgjdbc -- PostgreSQL

and despite what people often say, a .war deployed on jetty or tomcat
isn't exactly heavyweight.

Same idea as PgBouncer or PgPool. The advantage over hacking
PgBouncer/PgPool for the job is that Tomcat can already do a lot of what
you want using built-in, pre-existing functionality. Connection pool
management, low level REST-style HTTP processing, JSON handling etc are
all done for you.

> I do see now that PG uses one worker per connection rather than a worker
> pool as I had thought before. So there's nothing already in there to
> help me dispatch requests and making my own worker pool that distributes
> requests using sockets wouldn't be any better than connecting back using
> libpq.

Yep.

> A C frontend using libevent would be easy enough to make and deploy for
> this I guess.
> 
> But... Maybe nobody really wants this thing anyway, there seem to be
> some other options out there already.

It's something I think would be interesting to have, but IMO to be
really useful it'd need to support composing object graphs as json, a
json query format, etc. So you can say "get me this customer with all
their addresses and contact records" without having to issue a bunch of
queries (round trips) or use ORM-style left-join-and-deduplicate hacks
that waste bandwidth and are messy and annoying.

Just sending chunks of SQL and getting back arrays of tuples isn't very
interesting or, IMO, hugely useful for the kind of apps that want to
avoid JDBC/libpq. Especially webapps.

Close care to security and auth would also need to be taken. You don't
want to be sending a username/password with each request; you need a
reasonable authentication token system, request signing to prevent
replay attacks, idempotent requests, etc.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[David G Johnston]

On Mon, Sep 1, 2014 at 9:07 PM, Andrew Dunstan [via PostgreSQL] <
ml-node+s1045698n5817265...@n5.nabble.com> wrote:

>
> On 09/01/2014 08:09 PM, Neil Tiffin wrote:
> >
> > That should be enough alone to suggest postgreSQL start working on a
> modern, in core, fast, fully supported language.  Of course PL/pgSQL works,
> but so did one-line 5k perl programs that nobody likes today.  Everything
> can be done in assembler, but no one suggests that today.  Today, it is all
> about programmer productivity.  PL/pgSQL has a lot of unnecessary stuff
> that sucks the life out of programmer productivity.  And this should be
> very much a concern of the professionals that support PostgreSQL
> >
> > For example:
> >
> > DECLARE
> > declarations
> > BEGIN
> > statements
> > END
> >
> > This looks a lot like COBOL or Pascal, and today is mostly unnecessary.
>
> It looks like Ada, and that's not an accident. (Nor is it a bad thing.)
>
>
> The very last thing we should be doing is to invent a new language.
> There are already plenty to choose from.
>
> cheers
>
> andrew
>
>
​The extent of "plenty" narrows considerably if you factor in a requirement
for SQL to be treated as a first-class construct...

I would welcome the chance to evaluate an unencumbered language designed to
mesh with PostgreSQL​ specifically and that would greatly ease the effort
needed to write applications driven largely via in-database functions. Put
differently - how much effort do we want to making PostgreSQL an
irresistible platform that is difficult to leave?  Now, of course, and
salient to the point Andrew made, I'm not sure we actually have anyone with
the talent AND desire to actually create such a language - we haven't need
for a specialist language writer for a while now and I think you'd want a
specialist if you were to try and write a language from scratch (over even
adapt an existing language like what was apparently done with Ada).

The question here is whether the resources are available, if it was deemed
desirable, to even superficially overhaul pl/pgsql?

Random thought as I wrote that: how about considering how pl/pgsql
functionality can be generalize so that it is a database API that another
language can call?  In that way the server would drive the core
functionality and the language would simply be an interpreter that enforces
its specific notion of acceptable syntax.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817267.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

[HACKERS] orangutan seizes up during isolation-check

[Noah Misch]

Buildfarm member orangutan has failed chronically on both of the branches for
which it still reports, HEAD and REL9_1_STABLE, for over two years.  The
postmaster appears to jam during isolation-check.  Dave, orangutan currently
has one such jammed postmaster for each branch.  Could you gather some
information about the running processes?  Specifically, it would be helpful to
see the output of "ps -el" and a stack trace for each running PostgreSQL
process.  (If there are enough PostgreSQL processes to make stack traces
tedious to acquire, it will be almost as good to have traces for each
postmaster and one autovacuum worker per postmaster.)  Thanks.  Best not to
kill the processes yet, in case we need more information.


The rest of this message is just a dump my observations from the data already
available.  The jammed postmasters fail to complete fast shutdown requests.
Beyond that, the symptoms are different on HEAD versus 9.1.  The 2014-07-09
run is representative for HEAD.  multiple-row-versions.spec failed like this
after having run for almost 21 hours:

--- 1,2 
  Parsed test spec with 4 sessions
! Connection 2 to database failed: 
\ No newline at end of file

I don't know what would cause PQconnectdb() to hang for 21 hours before
failing with a blank error message.  Note that the hang duration and the spec
in which the hang falls varies from failure to failure.  All subsequent specs
then fail like this:

--- 1,4 
  Parsed test spec with 2 sessions
! Connection 0 to database failed: could not connect to server: Connection 
refused
!   Is the server running locally and accepting
!   connections on Unix domain socket "/tmp/.s.PGSQL.5678"?

One can get ECONNREFUSED from a Unix-domain socket when the listen() backlog
is full.  At this point, we've made only two connection attempts since the
last successful one and only about 40 attempts since last postmaster startup.
I have no good theories remaining at the moment.  The postmaster log ends in
1211 copies of this message:

WARNING:  worker took too long to start; canceled.

At the default autovacuum_naptime=1min, that represents 20:11:00 of autovacuum
launch failures.  The postmaster had been running about 20:55:42 by the time
we collected that log, suggesting that autovacuum was healthy until 40-45
minutes into the doomed PQconnectdb() call.  I'm hypothesizing that the
postmaster ceased serving autovacuum launcher requests.  A jammed postmaster
tends to explain both the ECONNREFUSED symptom and the autovacuum symptom.



In REL9_1_STABLE, isolation-check completes, but the StopDb-C:2 step that
follows isolation-check fails to stop the server.  (If you go back far enough
in the history, suites other than isolation-check occasionally jam the
server.)  The server log ends like this:

LOG:  received fast shutdown request
LOG:  aborting any active transactions
LOG:  autovacuum launcher shutting down

That suggests a postmaster stuck in PM_WAIT_BACKENDS.  The process data should
illuminate this situation.


-- 
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] PL/pgSQL 2

[Andrew Dunstan]

On 09/01/2014 08:09 PM, Neil Tiffin wrote:

On Sep 1, 2014, at 10:45 AM, Tom Lane  wrote:


What is actually being proposed, AFAICS, is a one-shot fix for a bunch
of unfortunate choices.  That might be worth doing, but let's not fool
ourselves about whether it’s one-shot or not.

Well, one shot every 18 years is not so bad.

I am only a casual user and as such probably do not merit much consideration 
from the experts here.  I only work with PL/pgSQL occasionally, but never go 
weeks without doing it and sometimes it is all I do for weeks.

That said and this being the internet, IMO working in PL/pgSQL is only slightly 
better than stabbing myself in the leg with a knife compared to other 
non-PL/pgSQL languages I work in.  Mostly my hate is directed at the silly 
quoting.  But it has lots of other quirks that are not all that obvious unless 
you work with it all day every day.

Now I could use other languages as was suggested upstream.  Lets see, I use R 
all the time, but R is not a first class language, not in core, and its slow. 
Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and 
don’t want to learn as no one else seems to use it (in my world anyway).  perl 
is the only possibility left and again, no one in my world is using Perl and 
it’s not clear if there is a performance penalty.  The docs say the best 
language for performance is PL/pgSQL after pure SQL.

Really, this is from the docs

a_output := a_output || '' if v_'' ||
 referrer_keys.kind || '' like ''
 || referrer_keys.key_string || ''
 then return ''  || referrer_keys.referrer_type
 || ‘'; end if;'';




The docs also tell you how to avoid having to do this, using dollar quoting.




That should be enough alone to suggest postgreSQL start working on a modern, in 
core, fast, fully supported language.  Of course PL/pgSQL works, but so did 
one-line 5k perl programs that nobody likes today.  Everything can be done in 
assembler, but no one suggests that today.  Today, it is all about programmer 
productivity.  PL/pgSQL has a lot of unnecessary stuff that sucks the life out 
of programmer productivity.  And this should be very much a concern of the 
professionals that support PostgreSQL

For example:

DECLARE
declarations
BEGIN
statements
END

This looks a lot like COBOL or Pascal, and today is mostly unnecessary.


It looks like Ada, and that's not an accident. (Nor is it a bad thing.)


The very last thing we should be doing is to invent a new language. 
There are already plenty to choose from.


cheers

andrew


--
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 for psql History Display on MacOSX

[Noah Misch]

On Mon, Sep 01, 2014 at 02:05:37PM -0400, Tom Lane wrote:
> Functionally this seems like a clear win over what we had, especially
> since it supports using the pager.  I'm inclined to think we should
> not only apply this change but back-patch it.
> 
> One thing worth thinking about: should we use a history_get() loop
> like this for *all* \s commands, even when the target file is a
> regular file not /dev/tty?

+1 for printing the same bytes regardless of destination.

> libedit's version of write_history does
> not write the history "in the clear" exactly, which you would think
> is the behavior wanted when saving a command history for any purpose
> other than updating ~/.psql_history.  Such a change would break a
> workflow that involves doing \s to some random file and then copying
> that file to ~/.psql_history, but I find it hard to fathom why anyone
> would do that.

I've not used \s apart from verifying that certain patches didn't break it.
(That "less ~/.psql_history" beats dumping thousands of lines to the tty was a
factor.)  "\s fname" is theoretically useful as an OS-independent alternative
to "cp ~/.psql_history fname".  I see too little certainty of net benefit to
justify a minor-release change to this.

On Mon, Sep 01, 2014 at 04:27:57PM -0400, Tom Lane wrote:

[history encoding change discussion]

> A disadvantage of fixing this is that psql versions containing the fix
> would be incompatible with versions without (since writing out a history
> file containing ^A in place of ^J, and not reversing that encoding upon
> reload, would lead to messed-up history data).  However, I have a feeling
> that we'd better proceed with a fix.  Sooner or later, somebody is going
> to point out to the libedit guys that they've emulated libreadline
> incorrectly.  If they fix that, we'll have a situation where psql's using
> different libedit versions are incompatible, which would be even more of
> a mess.

Yikes.  It's already painful to see libedit and GNU readline trash each
other's .psql_history files; let's not add a third format.  Long-term, psql
should cease relying on the history library to serialize and deserialize its
history file.  psql can then understand both formats, rewrite in the original
format, and use GNU format for new files.

Thanks,
nm


-- 
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] PL/pgSQL 2

[Neil Tiffin]

On Sep 1, 2014, at 10:45 AM, Tom Lane  wrote:

> What is actually being proposed, AFAICS, is a one-shot fix for a bunch
> of unfortunate choices.  That might be worth doing, but let's not fool
> ourselves about whether it’s one-shot or not.

Well, one shot every 18 years is not so bad.

I am only a casual user and as such probably do not merit much consideration 
from the experts here.  I only work with PL/pgSQL occasionally, but never go 
weeks without doing it and sometimes it is all I do for weeks.  

That said and this being the internet, IMO working in PL/pgSQL is only slightly 
better than stabbing myself in the leg with a knife compared to other 
non-PL/pgSQL languages I work in.  Mostly my hate is directed at the silly 
quoting.  But it has lots of other quirks that are not all that obvious unless 
you work with it all day every day.

Now I could use other languages as was suggested upstream.  Lets see, I use R 
all the time, but R is not a first class language, not in core, and its slow. 
Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and 
don’t want to learn as no one else seems to use it (in my world anyway).  perl 
is the only possibility left and again, no one in my world is using Perl and 
it’s not clear if there is a performance penalty.  The docs say the best 
language for performance is PL/pgSQL after pure SQL.

Really, this is from the docs

a_output := a_output || '' if v_'' ||
referrer_keys.kind || '' like ''
|| referrer_keys.key_string || ''
then return ''  || referrer_keys.referrer_type
|| ‘'; end if;'';

That should be enough alone to suggest postgreSQL start working on a modern, in 
core, fast, fully supported language.  Of course PL/pgSQL works, but so did 
one-line 5k perl programs that nobody likes today.  Everything can be done in 
assembler, but no one suggests that today.  Today, it is all about programmer 
productivity.  PL/pgSQL has a lot of unnecessary stuff that sucks the life out 
of programmer productivity.  And this should be very much a concern of the 
professionals that support PostgreSQL

For example:

DECLARE
declarations 
BEGIN
statements
END

This looks a lot like COBOL or Pascal, and today is mostly unnecessary.  

So my opinion would be to keep PL/pgSQL and adopt a new PL language that is 
fully supported, fast, and modern, that will over time replace PL/pgSQL.

Neil






-- 
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] PL/PgSQL: RAISE and the number of parameters

[Marko Tiikkaja]

On 2014-08-12 13:23, I wrote:

The compile-time raise parameter checking is a good move.

3 minor points:

- I would suggest to avoid "continue" within a loop so that the code is
simpler to understand, at least for me.


I personally find the code easier to read with the continue.


I've changed the loop slightly.  Do you find this more readable than the 
way the loop was previously written?



- I would suggest to update the documentation accordingly.


I've incorporated these changes into this version of the patch, with 
small changes.


On 2014-08-12 15:09, Fabien COELHO wrote:
> I'm not sure why elog is better than ereport in that case: ISTM that 
it is

> an error worth reporting if it ever happens, say there is another syntax
> added later on which is not caught for some reason by the compile-time
> check, so I would not change it.

With elog(ERROR, ..) it's still reported, but the user isn't fooled into 
thinking that the error is to be expected, and hopefully we would see a 
bug report.  If it's impossible to tell the two errors apart, we might 
have subtly broken code carried around for who knows how long.


Please let me know what you think about this patch.  Thanks for your 
work so far.



.marko
*** a/doc/src/sgml/plpgsql.sgml
--- b/doc/src/sgml/plpgsql.sgml
***
*** 3403,3408  RAISE ;
--- 3403,3411 
  Inside the format string, % is replaced by the
  string representation of the next optional argument's value. Write
  %% to emit a literal %.
+ The number of arguments must match the number of %
+ placeholders in the format string, or an error is raised during
+ the compilation of the function.
 
  
 
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
***
*** 2939,2948  exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt)
  	continue;
  }
  
  if (current_param == NULL)
! 	ereport(ERROR,
! 			(errcode(ERRCODE_SYNTAX_ERROR),
! 		  errmsg("too few parameters specified for RAISE")));
  
  paramvalue = exec_eval_expr(estate,
  	  (PLpgSQL_expr *) lfirst(current_param),
--- 2939,2947 
  	continue;
  }
  
+ /* should have been checked by the compiler */
  if (current_param == NULL)
! 	elog(ERROR, "unexpected RAISE parameter list length");
  
  paramvalue = exec_eval_expr(estate,
  	  (PLpgSQL_expr *) lfirst(current_param),
***
*** 2963,2976  exec_stmt_raise(PLpgSQL_execstate *estate, PLpgSQL_stmt_raise *stmt)
  appendStringInfoChar(&ds, cp[0]);
  		}
  
! 		/*
! 		 * If more parameters were specified than were required to process the
! 		 * format string, throw an error
! 		 */
  		if (current_param != NULL)
! 			ereport(ERROR,
! 	(errcode(ERRCODE_SYNTAX_ERROR),
! 	 errmsg("too many parameters specified for RAISE")));
  
  		err_message = ds.data;
  		/* No pfree(ds.data), the pfree(err_message) does it */
--- 2962,2970 
  appendStringInfoChar(&ds, cp[0]);
  		}
  
! 		/* should have been checked by the compiler */
  		if (current_param != NULL)
! 			elog(ERROR, "unexpected RAISE parameter list length");
  
  		err_message = ds.data;
  		/* No pfree(ds.data), the pfree(err_message) does it */
*** a/src/pl/plpgsql/src/pl_gram.y
--- b/src/pl/plpgsql/src/pl_gram.y
***
*** 106,111  static	void			 check_labels(const char *start_label,
--- 106,112 
  static	PLpgSQL_expr	*read_cursor_args(PLpgSQL_var *cursor,
  		  int until, const char *expected);
  static	List			*read_raise_options(void);
+ static	void			check_raise_parameters(PLpgSQL_stmt_raise *stmt);
  
  %}
  
***
*** 1849,1854  stmt_raise		: K_RAISE
--- 1850,1857 
  new->options = read_raise_options();
  		}
  
+ 		check_raise_parameters(new);
+ 
  		$$ = (PLpgSQL_stmt *)new;
  	}
  ;
***
*** 3768,3773  read_raise_options(void)
--- 3771,3810 
  }
  
  /*
+  * Check that the number of parameter placeholders in the message matches the
+  * number of parameters passed to it, if message was defined.
+  */
+ static void
+ check_raise_parameters(PLpgSQL_stmt_raise *stmt)
+ {
+ 	char *cp;
+ 	int expected_nparams = 0;
+ 
+ 	if (stmt->message == NULL)
+ 		return;
+ 
+ 	for (cp = stmt->message; *cp; cp++)
+ 	{
+ 		if (cp[0] != '%')
+ 			continue;
+ 		/* ignore literal % characters */
+ 		if (cp[1] == '%')
+ 			cp++;
+ 		else
+ 			expected_nparams++;
+ 	}
+ 
+ 	if (expected_nparams < list_length(stmt->params))
+ 		ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too many parameters specified for RAISE")));
+ 	if (expected_nparams > list_length(stmt->params))
+ 		ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("too few parameters specified for RAISE")));
+ }
+ 
+ /*
   * Fix up CASE statement
   */
  static PLpgSQL_stmt *
*** a/src/test/regress/expected/plpgsql.out
--- b/src/test/regress/expected/plpgsql.out
**

Re: [HACKERS] Patch for psql History Display on MacOSX

[Tom Lane]

I wrote:
>> Stepan Rutz  writes:
>>> Anyway, I am sure the iteration used in encode_history and decode_history 
>>> in input.c does not work on libedit.

>> Yeah, I noticed your comment about that.  That seems odd; a look at the
>> Apple libedit sources suggests it should work.  I was just about to trace
>> through the logic and try to see what's happening.

> Sigh ... the answer is that libedit has the direction of traversal
> backwards compared to libreadline.  If you replace next_history() by
> previous_history() in those loops, then it works as expected.

Oh, even *more* interesting: the existing coding seems to work as designed
in OS X Tiger.  I duplicated your result that it's broken on Mavericks
(that was what you were testing, no?).  I have a couple intermediate
Mac versions laying about, but no time to test them right now.

So apparently what we've got here is another episode in Apple's
nearly-unblemished record of shipping broken versions of libedit.
Sigh.  Either they have astonishingly bad luck at choosing when to
pull from the upstream sources, or the upstream sources are broken
most of the time.

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

[HACKERS] Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns

[Noah Misch]

On Sat, Aug 30, 2014 at 07:32:26PM -0400, Bruce Momjian wrote:
> On Wed, Aug 27, 2014 at 09:40:30PM -0400, Noah Misch wrote:
> > > > 3.  use the pg_dump binary-upgrade code when such cases happen
> > 
> > +1.  We have the convention that, while --binary-upgrade can inject catalog
> > hacks, regular pg_dump uses standard, documented DDL.  I like that 
> > convention
> > on general aesthetic grounds and for its benefit to non-superusers.  Let's
> > introduce the DDL needed to fix this bug while preserving that convention,
> > namely DDL to toggle attislocal.
> 
> I have spend some time researching this, and I am not sure what to
> recommend.  The basic issue is that CREATE TABLE INHERITS always puts
> the inherited columns first, so to preserve column ordering, you have to
> use CREATE TABLE and then ALTER TABLE INHERIT.  The problem there is
> that ALTER TABLE INHERIT doesn't preserve attislocal, and it also has
> problems with constraints not being marked local.  I am just not sure we
> want to add SQL-level code to do that.  Would it be documented?

Yes; I value the fact that ordinary pg_dump emits only documented SQL.  In a
similar vein, we added ALTER TABLE OF for the benefit of pg_dump.

> I have developed the attached patch to warn about column reordering in
> this odd case.  The patch mentions the reordering of c:

This, as amended downthread, seems useful.


-- 
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] PL/pgSQL 2

[Álvaro Hernández Tortosa]

On 01/09/14 23:46, David G Johnston wrote:

Álvaro Hernández Tortosa wrote

On 01/09/14 21:52, Joel Jacobson wrote:

On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa <

aht@
> wrote:

  What I can add is that, if Postgres is to devote resources to a new
language, I would plan it with a broader scope. What would attract most
users? Would it bring non postgres users to Postgres? What could be one
of
the killer features of any next version? My trivial answer to most of
these
questions is: PL/SQL. I don't know with detail how complex this is to
get in
Postgres (well, EDB probably knows), but if I had to chose a new
language,
this is it. So my questions would rather be:

Interesting visionary ideas.

For me personally, I have no Oracle functions to port to Postgres, so
all differences
between PL/SQL and PL/pgSQL would just be unnecessary extra amount of
work
in the process of porting existing code into a new language, be it
PL/SQL or PL/pgSQL 2.

That said, if PL/SQL wouldn't suffer from the problems I have with
PL/pgSQL today,
I wouldn't see a problem writing new code in the language, but then I
would probably
never manage to port all existing code to PL/SQL and I would be stuck
with a mix of code
in two languages instead of one. With PL/pgSQL 2 on the other hand, it
would be feasible
to eventually port all my existing code, as most of it would be
compatible without changes
and the rest would easy to make compatible.

I guess it's a question of if it's more important to recruit Oracle
users,
or if it's more important to improve the life of existing PL/pgSQL
Postgres users.

  I agree that for you, unfortunately, plpgsql2 would be better than
PL/SQL. However, I believe as a whole a bigger majority of users would
be benefited from this.

Is it even legal for us to create PL/SQL?
Honestly, I don't know. But I don't think so. And if it is, be it. 
Let's have Oracle sue us (who?)


Beyond that in all likelihood having both a version two of the pl/pgsql
language and the pl/SQL language would be a desireable outcome for, say, a
10.0 release.
If 10.0 is to follow 9.5, I'd agree. Althouth I'd also agree for 
any earlier, if that would be possible.


The former simply because languages by their very nature are evolutionary
and at some point the lost productivity of suppressing such evolution in the
name of backward compatibility will be deemed undesirable.  It may be
desirable to simply call the new language pl/elephant instead pl/pgsql2 but
the fundamental reason for evolving a language in order to incorporate newly
acquired knowledge is unarguable. Though in this case the entire
language/extension mechanism should be considered and not just the specific
procedural-SQL language we are dealing with here.

The goal of adding PL/SQL would be to increase the user base of the project
and hopefully attract new blood to the development team in order to maximize
long-term survivability and increase the pace of innovation.  We would be
unable to introduce substantial evolution to this language for that very
reason and so a different language is likely to be needed - eventually.


That's what I think. Increasing the user base, in a moment where 
for many reasons we all know many want to migrate off of Oracle... let's 
bring them here before they move to something else, with also piles of 
money and great marketing capabilities


Álvaro



--
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] PL/pgSQL 2

[Álvaro Hernández Tortosa]

On 01/09/14 23:31, Marko Tiikkaja wrote:

On 2014-09-01 11:11 PM, Álvaro Hernández Tortosa wrote:

  No, really: if there is a new version of a "language", which
modifies the current syntax of plpgsql; if plpgsql is already very
similar to PL/SQL: why not rather than coming up with a new syntax use
an already existing one? One that many, many more users than plpgsql,
already know?


The point isn't to create a new language just for the sake of creating 
a new one.  It's to fix the problems PL/PgSQL has.  If we're just 
going to trade the problems in PL/PgSQL with another set of problems 
implemented by PL/SQL, we're just worse off in the end.


Agreed. But if we can solve them --only if we could-- by leveraging 
a "syntax" that happens to be:


- Similar to that of plpgsql (exactly the same as plpgsql2 would be 
"similar" to plpgsql)

- Already known by a large, very large, group of users

we would be way better off. If there are unresolved problems in the 
PL/SQL current implementation, doing a superset of it may make sense.


Regards,


Álvaro



--
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] PL/pgSQL 2

[David G Johnston]

Álvaro Hernández Tortosa wrote
> On 01/09/14 21:52, Joel Jacobson wrote:
>> On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa <

> aht@

> > wrote:
>>>  What I can add is that, if Postgres is to devote resources to a new
>>> language, I would plan it with a broader scope. What would attract most
>>> users? Would it bring non postgres users to Postgres? What could be one
>>> of
>>> the killer features of any next version? My trivial answer to most of
>>> these
>>> questions is: PL/SQL. I don't know with detail how complex this is to
>>> get in
>>> Postgres (well, EDB probably knows), but if I had to chose a new
>>> language,
>>> this is it. So my questions would rather be:
>> Interesting visionary ideas.
>>
>> For me personally, I have no Oracle functions to port to Postgres, so
>> all differences
>> between PL/SQL and PL/pgSQL would just be unnecessary extra amount of
>> work
>> in the process of porting existing code into a new language, be it
>> PL/SQL or PL/pgSQL 2.
>>
>> That said, if PL/SQL wouldn't suffer from the problems I have with
>> PL/pgSQL today,
>> I wouldn't see a problem writing new code in the language, but then I
>> would probably
>> never manage to port all existing code to PL/SQL and I would be stuck
>> with a mix of code
>> in two languages instead of one. With PL/pgSQL 2 on the other hand, it
>> would be feasible
>> to eventually port all my existing code, as most of it would be
>> compatible without changes
>> and the rest would easy to make compatible.
>>
>> I guess it's a question of if it's more important to recruit Oracle
>> users,
>> or if it's more important to improve the life of existing PL/pgSQL
>> Postgres users.
> 
>  I agree that for you, unfortunately, plpgsql2 would be better than 
> PL/SQL. However, I believe as a whole a bigger majority of users would 
> be benefited from this.

Is it even legal for us to create PL/SQL?

Beyond that in all likelihood having both a version two of the pl/pgsql
language and the pl/SQL language would be a desireable outcome for, say, a
10.0 release.  

The former simply because languages by their very nature are evolutionary
and at some point the lost productivity of suppressing such evolution in the
name of backward compatibility will be deemed undesirable.  It may be
desirable to simply call the new language pl/elephant instead pl/pgsql2 but
the fundamental reason for evolving a language in order to incorporate newly
acquired knowledge is unarguable. Though in this case the entire
language/extension mechanism should be considered and not just the specific
procedural-SQL language we are dealing with here.

The goal of adding PL/SQL would be to increase the user base of the project
and hopefully attract new blood to the development team in order to maximize
long-term survivability and increase the pace of innovation.  We would be
unable to introduce substantial evolution to this language for that very
reason and so a different language is likely to be needed - eventually.

David J.



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817251.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
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] PL/pgSQL 2

[Marko Tiikkaja]

On 2014-09-01 11:11 PM, Álvaro Hernández Tortosa wrote:

  No, really: if there is a new version of a "language", which
modifies the current syntax of plpgsql; if plpgsql is already very
similar to PL/SQL: why not rather than coming up with a new syntax use
an already existing one? One that many, many more users than plpgsql,
already know?


The point isn't to create a new language just for the sake of creating a 
new one.  It's to fix the problems PL/PgSQL has.  If we're just going to 
trade the problems in PL/PgSQL with another set of problems implemented 
by PL/SQL, we're just worse off in the end.



.marko


--
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] PL/pgSQL 2

[Álvaro Hernández Tortosa]

On 01/09/14 21:52, Joel Jacobson wrote:

On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa  wrote:

 What I can add is that, if Postgres is to devote resources to a new
language, I would plan it with a broader scope. What would attract most
users? Would it bring non postgres users to Postgres? What could be one of
the killer features of any next version? My trivial answer to most of these
questions is: PL/SQL. I don't know with detail how complex this is to get in
Postgres (well, EDB probably knows), but if I had to chose a new language,
this is it. So my questions would rather be:

Interesting visionary ideas.

For me personally, I have no Oracle functions to port to Postgres, so
all differences
between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work
in the process of porting existing code into a new language, be it
PL/SQL or PL/pgSQL 2.

That said, if PL/SQL wouldn't suffer from the problems I have with
PL/pgSQL today,
I wouldn't see a problem writing new code in the language, but then I
would probably
never manage to port all existing code to PL/SQL and I would be stuck
with a mix of code
in two languages instead of one. With PL/pgSQL 2 on the other hand, it
would be feasible
to eventually port all my existing code, as most of it would be
compatible without changes
and the rest would easy to make compatible.

I guess it's a question of if it's more important to recruit Oracle users,
or if it's more important to improve the life of existing PL/pgSQL
Postgres users.


I agree that for you, unfortunately, plpgsql2 would be better than 
PL/SQL. However, I believe as a whole a bigger majority of users would 
be benefited from this.


If anyone is willing to look at the market numbers, the number of 
PL/SQL users compared to those of plpgsql is really huge. That would 
surely attract many more users to postgres, which would ultimately 
greatly help us all (in detriment of you and a few others, sure, but on 
a way that I'm sure it's manageable).


Of course, I'd +1 any attempt to build a super-set of PL/SQL that 
would try to implement, also, the request that you and others may have 
about this PL, should they make sense.


Regards,

Álvaro


--
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] PL/pgSQL 2

[Álvaro Hernández Tortosa]

On 01/09/14 21:08, Pavel Stehule wrote:




2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa >:



On 01/09/14 20:42, Tom Lane wrote:

=?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?= mailto:a...@nosys.es>> writes:

  What I can add is that, if Postgres is to devote
resources to a new
language, I would plan it with a broader scope. What would
attract most
users? Would it bring non postgres users to Postgres? What
could be one
of the killer features of any next version? My trivial
answer to most of
these questions is: PL/SQL.

By that I suppose you mean "I wish it would act just like Oracle".
The problem with such a wish is that a lot of the
incompatibilities
with Oracle are functions of the core SQL engine, not of the PL.
plpgsql already is about as close to PL/SQL as it's possible
to get
without changing core Postgres behavior --- or at least, that was
the original design desire, and I don't think that it's failed in
any large degree.

regards, tom lane


It's true that some of the incompatibilities are the core
engine, internal functions and so on, and that the plpgsql design
goal was to achieve "similarity". But similarity is not code
compatibility, and afaik, plpgsql is not code compatible with
PL/SQL. Having 1:1 code compatibility, if possible, is a very well
first step, only followed by the core functionalities you mention.

If postgres were going for a new language, why not implement
one which, having the other suggested functionality, also has 1:1
PL/SQL code compatibility? I'm sure it's no trivial task, but one
highly desirable.


It is false expectation - language is only one part .. and plpgsql 
isn't to far. There are different system of modules, different system 
of custom aggregates, mainly with PL/SQL is very complex library 
dbms_. This library is maybe more complex than current Postgres base.


OK. Understood. Full compatibility may be a longer-term goal. But 
why it's bad to have the same syntax at a language -not library- level?




It is task for commercial project --- not all Postgres users need a 
Oracle compatibility layer.


Certainly not all users need that layer. But I'm sure few would 
complain to have it.


Besides that, why do you say it is meant for a commercial project? 
If it is because postgres should not listen to users willing to migrate 
from Oracle --then we're screwed, losing the biggest opportunity (of 
attracting a large crowd of users) of recent times. If it is because 
it's too complex... well, I don't think the postgres community (as a 
whole) have less resources than commercial projects.



Next, I am sure, so it is in contradiction to Joel proposal.


That's not my business ;P

No, really: if there is a new version of a "language", which 
modifies the current syntax of plpgsql; if plpgsql is already very 
similar to PL/SQL: why not rather than coming up with a new syntax use 
an already existing one? One that many, many more users than plpgsql, 
already know?


Regards,

Álvaro

Re: [HACKERS] Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns

[Bruce Momjian]

On Mon, Sep  1, 2014 at 04:06:58PM -0400, Tom Lane wrote:
> Bruce Momjian  writes:
> > NOTICE:  moving and merging column "c" with inherited definition
> > DETAIL:  user-specified column moved to the location of the inherited
> > column
> 
> Dept of nitpicking: errdetail messages are supposed to be complete
> sentences, properly capitalized and punctuated.  Please re-read the
> style guidelines if you have forgotten them.

Oh, yeah;  updated patch attached.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
new file mode 100644
index 3720a0f..ece6b0f
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*** MergeAttributes(List *schema, List *supe
*** 1756,1767 
--- 1756,1771 
  	 */
  	if (inhSchema != NIL)
  	{
+ 		int		schema_attno = 0;
+ 
  		foreach(entry, schema)
  		{
  			ColumnDef  *newdef = lfirst(entry);
  			char	   *attributeName = newdef->colname;
  			int			exist_attno;
  
+ 			schema_attno++;
+ 
  			/*
  			 * Does it conflict with some previously inherited column?
  			 */
*** MergeAttributes(List *schema, List *supe
*** 1780,1788 
   * Yes, try to merge the two column definitions. They must
   * have the same type, typmod, and collation.
   */
! ereport(NOTICE,
!    (errmsg("merging column \"%s\" with inherited definition",
! 		   attributeName)));
  def = (ColumnDef *) list_nth(inhSchema, exist_attno - 1);
  typenameTypeIdAndMod(NULL, def->typeName, &defTypeId, &deftypmod);
  typenameTypeIdAndMod(NULL, newdef->typeName, &newTypeId, &newtypmod);
--- 1784,1797 
   * Yes, try to merge the two column definitions. They must
   * have the same type, typmod, and collation.
   */
!  if (exist_attno == schema_attno)
! 	ereport(NOTICE,
! 	   (errmsg("merging column \"%s\" with inherited definition",
! 			   attributeName)));
! else
! 	ereport(NOTICE,
! 	   (errmsg("moving and merging column \"%s\" with inherited definition", attributeName),
! 		errdetail("User-specified column moved to the location of the inherited column.")));
  def = (ColumnDef *) list_nth(inhSchema, exist_attno - 1);
  typenameTypeIdAndMod(NULL, def->typeName, &defTypeId, &deftypmod);
  typenameTypeIdAndMod(NULL, newdef->typeName, &newTypeId, &newtypmod);

-- 
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 for psql History Display on MacOSX

[Tom Lane]

I wrote:
> Stepan Rutz  writes:
>> Anyway, I am sure the iteration used in encode_history and decode_history in 
>> input.c does not work on libedit.

> Yeah, I noticed your comment about that.  That seems odd; a look at the
> Apple libedit sources suggests it should work.  I was just about to trace
> through the logic and try to see what's happening.

Sigh ... the answer is that libedit has the direction of traversal
backwards compared to libreadline.  If you replace next_history() by
previous_history() in those loops, then it works as expected.

> The reason nobody noticed is possibly that libedit doesn't actually need
> the newline-encoding hack.

Indeed, that's the reason.

> However, we should probably fix the loops if
> they aren't working as expected on libedit, just in case somebody tries
> to copy the logic for some other purpose.

We should either do that, or document what's actually going on here.

A disadvantage of fixing this is that psql versions containing the fix
would be incompatible with versions without (since writing out a history
file containing ^A in place of ^J, and not reversing that encoding upon
reload, would lead to messed-up history data).  However, I have a feeling
that we'd better proceed with a fix.  Sooner or later, somebody is going
to point out to the libedit guys that they've emulated libreadline
incorrectly.  If they fix that, we'll have a situation where psql's using
different libedit versions are incompatible, which would be even more of
a mess.

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] Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns

[Tom Lane]

Bruce Momjian  writes:
>   NOTICE:  moving and merging column "c" with inherited definition
>   DETAIL:  user-specified column moved to the location of the inherited
>   column

Dept of nitpicking: errdetail messages are supposed to be complete
sentences, properly capitalized and punctuated.  Please re-read the
style guidelines if you have forgotten them.

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] Patch for psql History Display on MacOSX

[Tom Lane]

Stepan Rutz  writes:
> Anyway, I am sure the iteration used in encode_history and decode_history in 
> input.c does not work on libedit.

Yeah, I noticed your comment about that.  That seems odd; a look at the
Apple libedit sources suggests it should work.  I was just about to trace
through the logic and try to see what's happening.

The reason nobody noticed is possibly that libedit doesn't actually need
the newline-encoding hack.  However, we should probably fix the loops if
they aren't working as expected on libedit, just in case somebody tries
to copy the logic for some other purpose.

Meanwhile, attached is a draft patch that uses the history_get loop for
all \s operations, and simplifies saveHistory in consequence.

regards, tom lane

diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index e16b4d5..e1949d8 100644
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** exec_command(const char *cmd,
*** 1088,1107 
  		char	   *fname = psql_scan_slash_option(scan_state,
     OT_NORMAL, NULL, true);
  
- #if defined(WIN32) && !defined(__CYGWIN__)
- 
- 		/*
- 		 * XXX This does not work for all terminal environments or for output
- 		 * containing non-ASCII characters; see comments in simple_prompt().
- 		 */
- #define DEVTTY	"con"
- #else
- #define DEVTTY	"/dev/tty"
- #endif
- 
  		expand_tilde(&fname);
! 		/* This scrolls off the screen when using /dev/tty */
! 		success = saveHistory(fname ? fname : DEVTTY, -1, false, false);
  		if (success && !pset.quiet && fname)
  			printf(_("Wrote history to file \"%s\".\n"), fname);
  		if (!fname)
--- 1088,1095 
  		char	   *fname = psql_scan_slash_option(scan_state,
     OT_NORMAL, NULL, true);
  
  		expand_tilde(&fname);
! 		success = printHistory(fname, pset.popt.topt.pager);
  		if (success && !pset.quiet && fname)
  			printf(_("Wrote history to file \"%s\".\n"), fname);
  		if (!fname)
diff --git a/src/bin/psql/input.c b/src/bin/psql/input.c
index aa32a3f..f43e4a2 100644
*** a/src/bin/psql/input.c
--- b/src/bin/psql/input.c
*** initializeInput(int flags)
*** 319,340 
  
  
  /*
!  * This function saves the readline history when user
!  * runs \s command or when psql exits.
   *
   * fname: pathname of history file.  (Should really be "const char *",
   * but some ancient versions of readline omit the const-decoration.)
   *
   * max_lines: if >= 0, limit history file to that many entries.
-  *
-  * appendFlag: if true, try to append just our new lines to the file.
-  * If false, write the whole available history.
-  *
-  * encodeFlag: whether to encode \n as \x01.  For \s calls we don't wish
-  * to do that, but must do so when saving the final history file.
   */
! bool
! saveHistory(char *fname, int max_lines, bool appendFlag, bool encodeFlag)
  {
  #ifdef USE_READLINE
  
--- 319,333 
  
  
  /*
!  * This function saves the readline history when psql exits.
   *
   * fname: pathname of history file.  (Should really be "const char *",
   * but some ancient versions of readline omit the const-decoration.)
   *
   * max_lines: if >= 0, limit history file to that many entries.
   */
! static bool
! saveHistory(char *fname, int max_lines)
  {
  #ifdef USE_READLINE
  
*** saveHistory(char *fname, int max_lines, 
*** 344,354 
  	 * where write_history will fail because it tries to chmod the target
  	 * file.
  	 */
! 	if (useHistory && fname &&
! 		strcmp(fname, DEVNULL) != 0)
  	{
! 		if (encodeFlag)
! 			encode_history();
  
  		/*
  		 * On newer versions of libreadline, truncate the history file as
--- 337,351 
  	 * where write_history will fail because it tries to chmod the target
  	 * file.
  	 */
! 	if (strcmp(fname, DEVNULL) != 0)
  	{
! 		/*
! 		 * Encode \n, since otherwise readline will reload multiline history
! 		 * entries as separate lines.  (libedit doesn't really need this, but
! 		 * we do it anyway since it's too hard to tell which implementation we
! 		 * are using.)
! 		 */
! 		encode_history();
  
  		/*
  		 * On newer versions of libreadline, truncate the history file as
*** saveHistory(char *fname, int max_lines, 
*** 362,368 
  		 * see if the write failed.  Similarly for append_history.
  		 */
  #if defined(HAVE_HISTORY_TRUNCATE_FILE) && defined(HAVE_APPEND_HISTORY)
- 		if (appendFlag)
  		{
  			int			nlines;
  			int			fd;
--- 359,364 
*** saveHistory(char *fname, int max_lines, 
*** 387,394 
  			if (errno == 0)
  return true;
  		}
! 		else
! #endif
  		{
  			/* truncate what we have ... */
  			if (max_lines >= 0)
--- 383,389 
  			if (errno == 0)
  return true;
  		}
! #else			/* don't have append support */
  		{
  			/* truncate what we have ... */
  			if (max_lines >= 0)
*** saveHistory(char *fname, int max_lines, 
*** 399,417 
  			if (errno == 0)
  return true;
  		}
  
  		psql_error("could not save history to file \"%s\"

Re: [HACKERS] Re: [BUGS] Re: BUG #9555: pg_dump for tables with inheritance recreates the table with the wrong order of columns

[Bruce Momjian]

On Sun, Aug 31, 2014 at 02:10:33PM -0400, Tom Lane wrote:
> David G Johnston  writes:
> > Would it be proper to issue an additional top-level warning with the column
> > moved notification?  Thus there would be NOTICE, NOTICE, WARNING in the
> > above example?  Or, more generically, "columns reordered to match inherited
> > column order" to avoid multiple warnings if more than one column is moved.
> 
> That's a good point: if this message fires at all, it will probably fire
> more than once; do we want that?  If we do it as you suggest here, we'll
> lose the information as to exactly which columns got relocated, which
> perhaps is bad, or maybe it doesn't matter.  Also, I don't remember the
> exact code structure in that area, but it might be a bit painful to
> arrange that we get only one such warning even when inheriting from
> multiple parents.
> 
> If we do want the specific moved columns to be identified, I'd still go
> with errdetail on the NOTICE rather than two separate messages.  I think
> calling it a WARNING is a bit extreme anyway.

OK, here is the updated output based on the comments:

CREATE TABLE B(a int, c int);
CREATE TABLE a5 (
a integer,
b integer,
c integer
)
INHERITS (b);
NOTICE:  merging column "a" with inherited definition
NOTICE:  moving and merging column "c" with inherited definition
DETAIL:  user-specified column moved to the location of the inherited
column

I think we have to mention "move" in the error message because
mentioning "move" only in the detail means that the detail actually has
new information, not more detailed information.

Patch attached.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
new file mode 100644
index 3720a0f..b88b664
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*** MergeAttributes(List *schema, List *supe
*** 1756,1767 
--- 1756,1771 
  	 */
  	if (inhSchema != NIL)
  	{
+ 		int		schema_attno = 0;
+ 
  		foreach(entry, schema)
  		{
  			ColumnDef  *newdef = lfirst(entry);
  			char	   *attributeName = newdef->colname;
  			int			exist_attno;
  
+ 			schema_attno++;
+ 
  			/*
  			 * Does it conflict with some previously inherited column?
  			 */
*** MergeAttributes(List *schema, List *supe
*** 1780,1788 
   * Yes, try to merge the two column definitions. They must
   * have the same type, typmod, and collation.
   */
! ereport(NOTICE,
!    (errmsg("merging column \"%s\" with inherited definition",
! 		   attributeName)));
  def = (ColumnDef *) list_nth(inhSchema, exist_attno - 1);
  typenameTypeIdAndMod(NULL, def->typeName, &defTypeId, &deftypmod);
  typenameTypeIdAndMod(NULL, newdef->typeName, &newTypeId, &newtypmod);
--- 1784,1797 
   * Yes, try to merge the two column definitions. They must
   * have the same type, typmod, and collation.
   */
!  if (exist_attno == schema_attno)
! 	ereport(NOTICE,
! 	   (errmsg("merging column \"%s\" with inherited definition",
! 			   attributeName)));
! else
! 	ereport(NOTICE,
! 	   (errmsg("moving and merging column \"%s\" with inherited definition", attributeName),
! 		errdetail("user-specified column moved to the location of the inherited column")));
  def = (ColumnDef *) list_nth(inhSchema, exist_attno - 1);
  typenameTypeIdAndMod(NULL, def->typeName, &defTypeId, &deftypmod);
  typenameTypeIdAndMod(NULL, newdef->typeName, &newTypeId, &newtypmod);

-- 
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] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 8:34 PM, Álvaro Hernández Tortosa  wrote:
> What I can add is that, if Postgres is to devote resources to a new
> language, I would plan it with a broader scope. What would attract most
> users? Would it bring non postgres users to Postgres? What could be one of
> the killer features of any next version? My trivial answer to most of these
> questions is: PL/SQL. I don't know with detail how complex this is to get in
> Postgres (well, EDB probably knows), but if I had to chose a new language,
> this is it. So my questions would rather be:

Interesting visionary ideas.

For me personally, I have no Oracle functions to port to Postgres, so
all differences
between PL/SQL and PL/pgSQL would just be unnecessary extra amount of work
in the process of porting existing code into a new language, be it
PL/SQL or PL/pgSQL 2.

That said, if PL/SQL wouldn't suffer from the problems I have with
PL/pgSQL today,
I wouldn't see a problem writing new code in the language, but then I
would probably
never manage to port all existing code to PL/SQL and I would be stuck
with a mix of code
in two languages instead of one. With PL/pgSQL 2 on the other hand, it
would be feasible
to eventually port all my existing code, as most of it would be
compatible without changes
and the rest would easy to make compatible.

I guess it's a question of if it's more important to recruit Oracle users,
or if it's more important to improve the life of existing PL/pgSQL
Postgres users.


-- 
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 for psql History Display on MacOSX

[Stepan Rutz]

Thanks Tom. This would help the poor mac-osx guys like me. I guess this is not 
that important because no one runs a production server on OS-X.  

Back patching to 9.3 won’t work as is, some minor conflict was there. 

Anyway, I am sure the iteration used in encode_history and decode_history in 
input.c does not work on libedit.

Regards from cologne,
Stepan


Am 01.09.2014 um 20:05 schrieb Tom Lane :

> Stepan Rutz  writes:
>> Attached is a very trivial patch as a basis for discussion that at least 
>> makes \s (show history) work in psql on Macs. Macs uses libedit, which has a 
>> libreadline interface. 
> 
> Hm.  The $64 question here is whether we can assume that history_get()
> exists and works compatibly in every interesting version of libreadline
> and libedit.
> 
> I poked into the oldest version of GNU readline I could find, 4.0
> (released in 1999), and that has it.  The oldest libedit I have around
> is the one that came with OSX 10.4 (the CVS marker in readline.h from
> that says 2004/01/17).  That has it too.  So that looks pretty good.
> 
> The readline code says that the argument ranges from "history_base"
> up, not from 1 up as this patch assumes.  And it looks like history_base
> can change once the max number of stored lines is exceeded, so we can't
> assume that 1 is good enough.  Fortunately, the global variable
> history_base also exists in both libraries (though it looks like it
> never changes from 1 in libedit).
> 
> Functionally this seems like a clear win over what we had, especially
> since it supports using the pager.  I'm inclined to think we should
> not only apply this change but back-patch it.
> 
> One thing worth thinking about: should we use a history_get() loop
> like this for *all* \s commands, even when the target file is a
> regular file not /dev/tty?  libedit's version of write_history does
> not write the history "in the clear" exactly, which you would think
> is the behavior wanted when saving a command history for any purpose
> other than updating ~/.psql_history.  Such a change would break a
> workflow that involves doing \s to some random file and then copying
> that file to ~/.psql_history, but I find it hard to fathom why anyone
> would do that.
> 
> There are a couple other minor bugs and some cosmetic things I don't like
> in this patch, but I'm willing to fix it up and commit it if there
> are not objections.
> 
>   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



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 8:38 PM, Tom Lane  wrote:
> c) plpgsql and plpgsql2 are the same code base, with a small number
> of places that act differently depending on the language version.

+1 to the idea


-- 
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] Built-in binning functions

[Petr Jelinek]

On 01/09/14 01:42, Tom Lane wrote:


BTW, was there a reason for not noticing the case of exact match in
the search loop, and falling out early?  As it stands the code will
reliably choose the leftmost match if there are multiple equal items
in the search array, but do we care about such cases?



I am not sure if we care, probably not.

Anyway I attached patch that I am happy with. I am not yet sure what to 
do with naming.


--
  Petr Jelinek  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index daa56e9..bda8386 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -901,25 +901,36 @@
 
  width_bucket
 
-width_bucket(op numeric, b1 numeric, b2 numeric, count int)
+width_bucket(operand numeric, b1 numeric, b2 numeric, count int)

int
return the bucket to which operand would
be assigned in an equidepth histogram with count
-   buckets, in the range b1 to b2
+   buckets spanning the range b1 to b2
width_bucket(5.35, 0.024, 10.06, 5)
3
   
 
   
-   width_bucket(op dp, b1 dp, b2 dp, count int)
+   width_bucket(operand dp, b1 dp, b2 dp, count int)
int
return the bucket to which operand would
be assigned in an equidepth histogram with count
-   buckets, in the range b1 to b2
+   buckets spanning the range b1 to b2
width_bucket(5.35, 0.024, 10.06, 5)
3
   
+
+  
+   width_bucket(operand anyelement, thresholds anyarray)
+   int
+   return the bucket to which operand would
+   be assigned given an array listing the upper bounds of the buckets;
+   the thresholds array must be sorted,
+   smallest first, or unexpected results will be obtained
+   width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])
+   2
+  
  
 

diff --git a/src/backend/utils/adt/arrayfuncs.c b/src/backend/utils/adt/arrayfuncs.c
index f8e94ec..57376ea 100644
--- a/src/backend/utils/adt/arrayfuncs.c
+++ b/src/backend/utils/adt/arrayfuncs.c
@@ -15,8 +15,10 @@
 #include "postgres.h"
 
 #include 
+#include 
 
 #include "access/htup_details.h"
+#include "catalog/pg_type.h"
 #include "funcapi.h"
 #include "libpq/pqformat.h"
 #include "utils/array.h"
@@ -130,6 +132,15 @@ static ArrayType *array_replace_internal(ArrayType *array,
 	   Datum replace, bool replace_isnull,
 	   bool remove, Oid collation,
 	   FunctionCallInfo fcinfo);
+static int width_bucket_fixed(Datum operand,
+   ArrayType *thresholds,
+   Oid collation,
+   TypeCacheEntry *typentry);
+static int width_bucket_fixed_float8(Datum operand, ArrayType *thresholds);
+static int width_bucket_variable(Datum operand,
+	  ArrayType *thresholds,
+	  Oid collation,
+	  TypeCacheEntry *typentry);
 
 
 /*
@@ -5502,3 +5513,219 @@ array_replace(PG_FUNCTION_ARGS)
    fcinfo);
 	PG_RETURN_ARRAYTYPE_P(array);
 }
+
+/*
+ * Implements width_bucket(anyelement, anyarray).
+ *
+ * 'thresholds' is an array containing upper bound values for each bucket;
+ * these must be sorted from smallest to largest, or bogus results will be
+ * produced.  If N thresholds are supplied, the output is from 0 to N:
+ * 0 is for inputs < first threshold, N is for inputs >= last threshold.
+ */
+Datum
+width_bucket_generic(PG_FUNCTION_ARGS)
+{
+	Datum		operand = PG_GETARG_DATUM(0);
+	ArrayType  *thresholds = PG_GETARG_ARRAYTYPE_P(1);
+	Oid			element_type = ARR_ELEMTYPE(thresholds);
+	int			result;
+
+	/* Check input */
+	if (ARR_NDIM(thresholds) > 1)
+		ereport(ERROR,
+(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+ errmsg("thresholds must be one-dimensional array")));
+
+	if (array_contains_nulls(thresholds))
+		ereport(ERROR,
+(errcode(ERRCODE_NULL_VALUE_NOT_ALLOWED),
+ errmsg("thresholds array must not contain NULLs")));
+
+	/* For float8 use optimized implementation */
+	if (element_type == FLOAT8OID)
+		result = width_bucket_fixed_float8(operand, thresholds);
+	else
+	{
+		Oid			collation = PG_GET_COLLATION();
+		TypeCacheEntry *typentry;
+
+		/* Cache information about the input type */
+		typentry = (TypeCacheEntry *) fcinfo->flinfo->fn_extra;
+		if (typentry == NULL ||
+			typentry->type_id != element_type)
+		{
+			typentry = lookup_type_cache(element_type,
+		 TYPECACHE_CMP_PROC_FINFO);
+			if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid))
+ereport(ERROR,
+		(errcode(ERRCODE_UNDEFINED_FUNCTION),
+   errmsg("could not identify a comparison function for type %s",
+		  format_type_be(element_type;
+			fcinfo->flinfo->fn_extra = (void *) typentry;
+		}
+
+		/* We have two generic implementation paths for fixed- and variable-width types */
+		if (typentry->typlen > 0)
+			result = width_bucket_fixed(operand, thresholds, collation, typentry);
+		else
+			result = width_bucket_va

Re: [HACKERS] PL/pgSQL 2

[Pavel Stehule]

2014-09-01 20:58 GMT+02:00 Álvaro Hernández Tortosa :

>
> On 01/09/14 20:42, Tom Lane wrote:
>
>> =?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?=  writes:
>>
>>>   What I can add is that, if Postgres is to devote resources to a new
>>> language, I would plan it with a broader scope. What would attract most
>>> users? Would it bring non postgres users to Postgres? What could be one
>>> of the killer features of any next version? My trivial answer to most of
>>> these questions is: PL/SQL.
>>>
>> By that I suppose you mean "I wish it would act just like Oracle".
>> The problem with such a wish is that a lot of the incompatibilities
>> with Oracle are functions of the core SQL engine, not of the PL.
>> plpgsql already is about as close to PL/SQL as it's possible to get
>> without changing core Postgres behavior --- or at least, that was
>> the original design desire, and I don't think that it's failed in
>> any large degree.
>>
>> regards, tom lane
>>
>
> It's true that some of the incompatibilities are the core engine,
> internal functions and so on, and that the plpgsql design goal was to
> achieve "similarity". But similarity is not code compatibility, and afaik,
> plpgsql is not code compatible with PL/SQL. Having 1:1 code compatibility,
> if possible, is a very well first step, only followed by the core
> functionalities you mention.
>
> If postgres were going for a new language, why not implement one
> which, having the other suggested functionality, also has 1:1 PL/SQL code
> compatibility? I'm sure it's no trivial task, but one highly desirable.
>

It is false expectation - language is only one part .. and plpgsql isn't to
far. There are different system of modules, different system of custom
aggregates, mainly with PL/SQL is very complex library dbms_. This
library is maybe more complex than current Postgres base.

It is task for commercial project --- not all Postgres users need a Oracle
compatibility layer. Next, I am sure, so it is in contradiction to Joel
proposal.

Regards

Pavel


>
> Regards,
>
> Álvaro
>
>
>
>
> --
> 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] PL/pgSQL 2

[Álvaro Hernández Tortosa]

On 01/09/14 20:42, Tom Lane wrote:

=?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?=  writes:

  What I can add is that, if Postgres is to devote resources to a new
language, I would plan it with a broader scope. What would attract most
users? Would it bring non postgres users to Postgres? What could be one
of the killer features of any next version? My trivial answer to most of
these questions is: PL/SQL.

By that I suppose you mean "I wish it would act just like Oracle".
The problem with such a wish is that a lot of the incompatibilities
with Oracle are functions of the core SQL engine, not of the PL.
plpgsql already is about as close to PL/SQL as it's possible to get
without changing core Postgres behavior --- or at least, that was
the original design desire, and I don't think that it's failed in
any large degree.

regards, tom lane


It's true that some of the incompatibilities are the core engine, 
internal functions and so on, and that the plpgsql design goal was to 
achieve "similarity". But similarity is not code compatibility, and 
afaik, plpgsql is not code compatible with PL/SQL. Having 1:1 code 
compatibility, if possible, is a very well first step, only followed by 
the core functionalities you mention.


If postgres were going for a new language, why not implement one 
which, having the other suggested functionality, also has 1:1 PL/SQL 
code compatibility? I'm sure it's no trivial task, but one highly desirable.


Regards,

Álvaro



--
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] PL/pgSQL 2

[Tom Lane]

=?UTF-8?B?w4FsdmFybyBIZXJuw6FuZGV6IFRvcnRvc2E=?=  writes:
>  What I can add is that, if Postgres is to devote resources to a new 
> language, I would plan it with a broader scope. What would attract most 
> users? Would it bring non postgres users to Postgres? What could be one 
> of the killer features of any next version? My trivial answer to most of 
> these questions is: PL/SQL.

By that I suppose you mean "I wish it would act just like Oracle".
The problem with such a wish is that a lot of the incompatibilities
with Oracle are functions of the core SQL engine, not of the PL.
plpgsql already is about as close to PL/SQL as it's possible to get
without changing core Postgres behavior --- or at least, that was
the original design desire, and I don't think that it's failed in
any large degree.

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] PL/pgSQL 2

[Tom Lane]

Joel Jacobson  writes:
> I see two possible approaches of a plpgsql2 project, both aiming to
> require minimal/no changes of most existing best-practice plpgsql
> code:
> a) fork plpgsql code base and implement changes with as few lines of
> code as possible, making it easier to understand the changes, verify
> their correctness and apply future patches of the plpgsql code.
> b) fork plpgsql code and remove as much code as possible thanks to the
> reduced complexity possible thanks to the stricter behaviour achieved
> by removing settings and enforcing a stricter coding convention and
> killing obsolete quirks.

You lost me at "fork".

Reality is that plpgsql development is not going to stop; wherever
possible we are going to want to put new features into both languages,
and bug fixes even more so.  Moreover, the size of the changes you've
suggested hardly merits a fork in the first place, and the notion that
we're going to be able to radically simplify the code base after making
them is laughable.  What I would think about is

c) plpgsql and plpgsql2 are the same code base, with a small number
of places that act differently depending on the language version.

We could alternatively get the result by inventing a bunch of pragma
declarations, or some similar notation, that control the behavioral
changes one-at-a-time.  That might even be worth doing anyway, in
case somebody likes some of the ideas and others not so much.  But
I'd see the language version as a convenient shorthand for enabling a
specified collection of pretty-localized incompatible behavior changes.
If they're not pretty localized, there's going to be a barrier to
uptake, very comparable to the python3 analogy mentioned upthread.

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] PL/pgSQL 2

[Pavel Stehule]

2014-09-01 20:23 GMT+02:00 Joel Jacobson :

> On Mon, Sep 1, 2014 at 5:45 PM, Tom Lane  wrote:
> > What is actually being proposed, AFAICS, is a one-shot fix for a bunch
> > of unfortunate choices.  That might be worth doing, but let's not fool
> > ourselves about whether it's one-shot or not.
>
> I'm glad to hear you think it *might* be worth doing.
> A one-shot is exactly what it is, like a new major version of postgres
> itself (but a new major version of postgres has a much longer release
> note of changes :).
> Once released, there is obviously no way to include new non-backwards
> compatible code in future minor versions.
>
> I guess it boils down to if the project can agree on if there are any
> significant *important* changes worth doing that are *not* possible or
> feasible to implement in plpgsql.
>
> I see two possible approaches of a plpgsql2 project, both aiming to
> require minimal/no changes of most existing best-practice plpgsql
> code:
> a) fork plpgsql code base and implement changes with as few lines of
> code as possible, making it easier to understand the changes, verify
> their correctness and apply future patches of the plpgsql code.
> b) fork plpgsql code and remove as much code as possible thanks to the
> reduced complexity possible thanks to the stricter behaviour achieved
> by removing settings and enforcing a stricter coding convention and
> killing obsolete quirks.
>
>
I don't like a idea so we will have plpgsql 2x

without significant redesign you don't throw too much lines. If you really
need to design new language, then redesign engine first.




> Given plpgsql2 is a one-shot, the time window to gather input of what
> non-compatible changes to include probably needs to be at least a
> year.
> During that period, the mostly-compatible changes discussed could be
> implemented, which are the ones I'm personally most interested in
> anyway, but if we are creating a new language, then naturally we
> should take the chance to include all important changes we wish we
> could do but cannot with plpgsql.
>

Re: [HACKERS] PL/pgSQL 2

[Álvaro Hernández Tortosa]

On 01/09/14 14:27, Joel Jacobson wrote:

On Mon, Sep 1, 2014 at 1:30 PM, Pavel Stehule  wrote:

I agree with Andres - it is not a good for plpgsql and for plpgsql users.
The benefit must be significant for 90% of users.

...

Official implementation of plpgsql2 can be very wrong and dangerous signal -
so we should not to do.

Do you argue the introduction of plpgsql2 would hurt the users of
plpgsql in some way? How?

If you have X% who continue to happily use plpgsql, and (100-X%) who
find they can use plpgsql2 in their project, for new functions or all
functions (for a new project), then you have made (100-X)% of the
users more happy, than they would be if they were forced to use
plpgsql and suffer from its problems.

It *would* be a problem if you had to choose between writing all
functions in their plpgsql or plpgsql2, but thanks to postgres support
for different pl-languages and mixing different languages in the same
project, I cannot see the problem.


What it's clear from my "non-hacker, casual hackers ml reader" 
opinion here, is that there is room for new language features or a new 
in-core language at once. I find Joel's reasoning quite clear about the 
general concepts of improving on plpgsql, although the precise changes 
may not be big enough to justify just a new version. But if there are 
enough changes, and breaking compatibility with the current plpgsql is a 
major concern, I fail to buy other arguments of why doing plpgsql2 is a 
bad thing. The comparisons with Python/Perl are very misleading, as they 
have nothing to do with Postgres, and the case is obviously different.


What I can add is that, if Postgres is to devote resources to a new 
language, I would plan it with a broader scope. What would attract most 
users? Would it bring non postgres users to Postgres? What could be one 
of the killer features of any next version? My trivial answer to most of 
these questions is: PL/SQL. I don't know with detail how complex this is 
to get in Postgres (well, EDB probably knows), but if I had to chose a 
new language, this is it. So my questions would rather be:


- Is it feasible (resources, time, interest) to implement PL/SQL in 
Postgres?
- Does it support all the requested new features Joel and others 
mentioned in this thread as desires for the new language?
- If the answer to the previous question is no, could those unsupported 
features be implemented as a compatible superset of PL/SQL?


Sorry if this sounds too unconventional for this list, but this is 
what IMVHO many users would be more pleased with.


My 2 cents,

Álvaro


--
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: proposal: ignore null fields in not relation type composite type based constructors

[Pavel Stehule]

2014-09-01 12:33 GMT+02:00 Jeevan Chalke :

> Hi Pavel,
>
> Patch does look good to me. And found no issues as such.
>
> However here are my optional suggestions:
>
> 1. Frankly, I did not like name of the function
> "row_to_json_pretty_choosy".
> Something like "row_to_json_pretty_ignore_nulls" seems better to me.
>

should be - I have no better name


>
> 2. To use ignore nulls feature, I have to always pass pretty flag.
> Which seems weired.
>
> Since we do support named argument, can we avoid that?
> No idea how much difficult it is. If we have a default arguments to this
> function then we do not need one and two argument variations for this
> function as well. And we can use named argument for omitting the required
> one. Just a thought.
>

it needs a redesign of original implementation, we should to change API to
use default values with named parameters

but it doesn't help too much (although it can be readable little bit more)

instead row_to_json(x, false, true)

be

row_ro_json(x, ignore_null := true)

it is not too much work, but I need a names for parameters

Regards

Pavel


>
> Rest looks good to me.
>
>
> Thanks
>
> --
> Jeevan B Chalke
> Principal Software Engineer, Product Development
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
>
>

Re: [HACKERS] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 5:45 PM, Tom Lane  wrote:
> What is actually being proposed, AFAICS, is a one-shot fix for a bunch
> of unfortunate choices.  That might be worth doing, but let's not fool
> ourselves about whether it's one-shot or not.

I'm glad to hear you think it *might* be worth doing.
A one-shot is exactly what it is, like a new major version of postgres
itself (but a new major version of postgres has a much longer release
note of changes :).
Once released, there is obviously no way to include new non-backwards
compatible code in future minor versions.

I guess it boils down to if the project can agree on if there are any
significant *important* changes worth doing that are *not* possible or
feasible to implement in plpgsql.

I see two possible approaches of a plpgsql2 project, both aiming to
require minimal/no changes of most existing best-practice plpgsql
code:
a) fork plpgsql code base and implement changes with as few lines of
code as possible, making it easier to understand the changes, verify
their correctness and apply future patches of the plpgsql code.
b) fork plpgsql code and remove as much code as possible thanks to the
reduced complexity possible thanks to the stricter behaviour achieved
by removing settings and enforcing a stricter coding convention and
killing obsolete quirks.

Given plpgsql2 is a one-shot, the time window to gather input of what
non-compatible changes to include probably needs to be at least a
year.
During that period, the mostly-compatible changes discussed could be
implemented, which are the ones I'm personally most interested in
anyway, but if we are creating a new language, then naturally we
should take the chance to include all important changes we wish we
could do but cannot with plpgsql.


-- 
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] implement subject alternative names support for SSL connections

[Alexey Klyukin]

On Mon, Sep 1, 2014 at 10:39 AM, Alexey Klyukin  wrote:
> On Fri, Aug 29, 2014 at 11:22 AM, Heikki Linnakangas
>  wrote:
>> Yeah, I think a certificate without CN should be supported. See also RFC 
>> 6125, section 4.1. "Rules" [for issuers of certificates]:
>>
>>>5.  Even though many deployed clients still check for the CN-ID
>>>within the certificate subject field, certification authorities
>>>are encouraged to migrate away from issuing certificates that
>>>represent the server's fully qualified DNS domain name in a
>>>CN-ID.  Therefore, the certificate SHOULD NOT include a CN-ID
>>>unless the certification authority issues the certificate in
>>>accordance with a specification that reuses this one and that
>>>explicitly encourages continued support for the CN-ID identifier
>>>type in the context of a given application technology.
>>
>>
>> Certificates without a CN-ID are probably rare today, but they might start 
>> to appear in the future.
>
> Ok, I will change a patch to add support for this clause.

Attached is a new version. I've changed the logic to check for the SAN
names first, and only check the common name if there is no match. The
error when the common name is missing is only shown if SAN section
does not contain any DNS names as well. The tricky part is the error
message if no match was found: initially, it only listed a single
common name, but now tracking all DNS names just for the sake of the
error message makes the code more bloated, so I'm wondering if simply
stating that there was no match, as implemented in the attached patch,
would be good enough?

-- 
Regards,
Alexey Klyukin
diff --git a/src/interfaces/libpq/fe-secure-openssl.c 
b/src/interfaces/libpq/fe-secure-openssl.c
new file mode 100644
index f950fc3..a4e3fc6
*** a/src/interfaces/libpq/fe-secure-openssl.c
--- b/src/interfaces/libpq/fe-secure-openssl.c
***
*** 60,68 
--- 60,73 
  #ifdef USE_SSL_ENGINE
  #include 
  #endif
+ #include 
  
  static bool verify_peer_name_matches_certificate(PGconn *);
  static intverify_cb(int ok, X509_STORE_CTX *ctx);
+ static intcertificate_name_entry_validate_match(PGconn *conn,
+   
  char *name,
+   
  unsigned int len,
+   
  bool *match);
  static void destroy_ssl_system(void);
  static intinitialize_SSL(PGconn *conn);
  static PostgresPollingStatusType open_client_SSL(PGconn *);
*** wildcard_certificate_match(const char *p
*** 471,487 
return 1;
  }
  
  
  /*
!  *Verify that common name resolves to peer.
   */
  static bool
  verify_peer_name_matches_certificate(PGconn *conn)
  {
!   char   *peer_cn;
!   int r;
!   int len;
!   boolresult;
  
/*
 * If told not to verify the peer name, don't do it. Return true
--- 476,525 
return 1;
  }
  
+ /*
+  * Validate a single certificate name entry and match it against the pghost.
+  * Returns 0 if the certificate name is invalid (contains embedded NULLs), 1 
otherwise.
+  */
+ static int
+ certificate_name_entry_validate_match(PGconn *conn, char *name, unsigned int 
len, bool *match)
+ {
+   /* There is no guarantee the string returned from the certificate is 
NULL-terminated */
+   name[len] = '\0';
+   *match = false;
+   /*
+* Reject embedded NULLs in certificate common or alternative name to 
prevent attacks
+* like CVE-2009-4034.
+*/
+   if (len != strlen(name))
+   {
+   printfPQExpBuffer(&conn->errorMessage,
+ libpq_gettext("SSL certificate's 
common name contains embedded null\n"));
+   return 0;
+   }
+   if (pg_strcasecmp(name, conn->pghost) == 0)
+   /* Exact name match */
+   *match = true;
+   else if (wildcard_certificate_match(name, conn->pghost))
+   /* Matched wildcard certificate */
+   *match = true;
+   else
+   *match = false;
+   return 1;
+ }
+ 
  
  /*
!  *Verify that common name or any of the alternative DNS names resolves to 
peer.
!  *Names in Subject Alternative Names and Common Name if present are 
considered.
   */
  static bool
  verify_peer_name_matches_certificate(PGconn *conn)
  {
!   inti;
!   intsan_len;
!   bool   result;
!   bool   san_has_dns_names;
!   STACK_OF(GENERAL_NAME) *peer_san;
  
/*
 * If told not to verify the peer name, don't do it. Return true
*** verif

Re: [HACKERS] delta relations in AFTER triggers

[Tom Lane]

Heikki Linnakangas  writes:
> On 08/30/2014 12:15 AM, Kevin Grittner wrote:
>> If we were to go with the hooks as you propose, we would still need
>> to take the information from TriggerData and put it somewhere else
>> for the hook to reference.

> Sure.

FWIW, I agree with Heikki on this point.  It makes a lot more sense for
the parser to provide hooks comparable to the existing hooks for resolving
column refs, and it's not apparent that loading such functionality into
SPI is sane at all.

OTOH, I agree with Kevin that the things we're talking about are
lightweight relations not variables.

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] Patch for psql History Display on MacOSX

[Tom Lane]

Stepan Rutz  writes:
> Attached is a very trivial patch as a basis for discussion that at least 
> makes \s (show history) work in psql on Macs. Macs uses libedit, which has a 
> libreadline interface. 

Hm.  The $64 question here is whether we can assume that history_get()
exists and works compatibly in every interesting version of libreadline
and libedit.

I poked into the oldest version of GNU readline I could find, 4.0
(released in 1999), and that has it.  The oldest libedit I have around
is the one that came with OSX 10.4 (the CVS marker in readline.h from
that says 2004/01/17).  That has it too.  So that looks pretty good.

The readline code says that the argument ranges from "history_base"
up, not from 1 up as this patch assumes.  And it looks like history_base
can change once the max number of stored lines is exceeded, so we can't
assume that 1 is good enough.  Fortunately, the global variable
history_base also exists in both libraries (though it looks like it
never changes from 1 in libedit).

Functionally this seems like a clear win over what we had, especially
since it supports using the pager.  I'm inclined to think we should
not only apply this change but back-patch it.

One thing worth thinking about: should we use a history_get() loop
like this for *all* \s commands, even when the target file is a
regular file not /dev/tty?  libedit's version of write_history does
not write the history "in the clear" exactly, which you would think
is the behavior wanted when saving a command history for any purpose
other than updating ~/.psql_history.  Such a change would break a
workflow that involves doing \s to some random file and then copying
that file to ~/.psql_history, but I find it hard to fathom why anyone
would do that.

There are a couple other minor bugs and some cosmetic things I don't like
in this patch, but I'm willing to fix it up and commit it if there
are not objections.

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] On partitioning

[Hannu Krosing]

On 09/01/2014 05:52 PM, Andres Freund wrote:
> On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
>> It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
>> pagenr, tupnr).
>>
>> Cross-partition indexes are especially needed if we want to allow putting
>> UNIQUE constraints on non-partition-key columns.
> I actually don't think this is necessary. I'm pretty sure that you can
> build an efficient and correct version of unique constraints with
> several underlying indexes in different partitions each. The way
> exclusion constraints are implemented imo is a good guide.
>
> I personally think that implementing cross partition indexes has a low
> enough cost/benefit ratio that I doubt it's wise to tackle it anytime
> soon.
Also it has the downside of (possibly) making DROP PARTITION either
slow or wasting space until next VACUUM.

So if building composite unique indexes over multiple per-partition
indexes is doable, I would much prefer this.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] delta relations in AFTER triggers

[Heikki Linnakangas]

On 08/30/2014 12:15 AM, Kevin Grittner wrote:

Heikki Linnakangas  wrote:

On 08/28/2014 12:03 AM, Kevin Grittner wrote:

Heikki Linnakangas  wrote:

I suggest adding a new hook to the ParseState struct, (p_rangevar_hook
?). The planner calls it whenever it sees a reference to a table, and
the hook function returns back some sort of placeholder reference to the
tuplestore. With variables, the hook returns a Param node, and at
execution time, the executor calls the paramFetch hook to fetch the
value of the param. For relations/tuplestores, I guess we'll need to
invent something like a Param node, but for holding information about
the relation. Like your TsrData struct, but without the pointer to the
tuplestore. At execution time, in the SPI_execute call, you pass the
pointer to the tuplestore in the ParamListInfo struct, like you pass
parameter values.

Does this make sense?


I see your point, but SPI first has to be made aware of the
tuplestores and their corresponding names and TupleDesc structures.
Does it make sense to keep the SPI_register_tuplestore() and
SPI_unregister_tuplestore() functions for the client side of the
API, and pass things along to the parse analysis through execution
phases using the techniques you describe?


Sorry, I didn't understand that. What do you mean by "first", and the
"client side of the API"? I don't see any need for the
SPI_register_tuplestore() and and SPI_unregister_tuplestore() functions
if you use the hooks.


If we were to go with the hooks as you propose, we would still need
to take the information from TriggerData and put it somewhere else
for the hook to reference.


Sure.


The hooks are generalized for plpgsql,
not just for triggers, and it doesn't seem appropriate for them to
be fishing around in the TriggerData structure.


PLpgSQL_execstate seems like the appropriate place.


And what if we add other sources for tuplestores?


What about it?


The lookup during parse analysis
each time an apparent relation name is encountered must be simple
and fast.


We already use hooks for ColumnRefs, which are called even more often, 
and we haven't had a problem making that fast enough.



I want named tuplestores to be easy to use from *all* PLs (for
trigger usage) as well as useful for other purposes people may want
to develop.


I'm not sure other PLs would even want to resolve the old/new relations 
like PL/pgSQL does. It might be more natural to access the new/old 
tuplestores as perl or python hashes or arrays, for example. But if they 
do, it's not that difficult to write the hooks.



I had to change the hashkey for plpgsql's plan
caching, but that needs to be done regardless of the API (to
prevent problems in the obscure case that someone attaches the same
trigger function to the same table for the same events more than
once with different trigger names and different transition table
names).  If you ignore that, the *entire* change to use this in
plpgsql is to add these lines to plpgsql_exec_trigger():

 /*
  * Capture the NEW and OLD transition TABLE tuplestores (if specified for
  * this trigger).
  */
 if (trigdata->tg_newtable)
 {
 Tsr tsr = palloc(sizeof(TsrData));

 tsr->name = trigdata->tg_trigger->tgnewtable;
 tsr->tstate = trigdata->tg_newtable;
 tsr->tupdesc = trigdata->tg_relation->rd_att;
 tsr->relid = trigdata->tg_relation->rd_id;
 SPI_register_tuplestore(tsr);
 }
 if (trigdata->tg_oldtable)
 {
 Tsr tsr = palloc(sizeof(TsrData));

 tsr->name = trigdata->tg_trigger->tgoldtable;
 tsr->tstate = trigdata->tg_oldtable;
 tsr->tupdesc = trigdata->tg_relation->rd_att;
 tsr->relid = trigdata->tg_relation->rd_id;
 SPI_register_tuplestore(tsr);
 }

With the new SPI functions, the code to implement this in each
other PL should be about the same (possibly identical), and areas
using SPI only need similar code to make tuplestores visible to the
planner and usable in the executor if someone has another use for
this.  You just do the above once you have run SPI_connect() and
before preparing or executing any query that references the named
tuplestore.


With hooks, the code to implement them in other PLs would be about the 
same too, if they want the same behavior.



 It remains available on that SPI connection until
SPI_finish() is called or you explicitly unregister it (by name).


Yeah, I don't like that. The SPI interface is currently stateless. Well, 
except for cursors and plans explicitly saved with SPI_keepplan. But the 
way queries are parsed is stateless - you pass all the necessary 
information as part of the SPI_execute call (or similar), using direct 
arguments and the ParamListInfo struct.



If you don't want to use hooks, I nevertheless feel that the old/new 
relations should be passed as part of the ParamListInfo struct, one way 
or another. With hooks, you would set the parserSetup hook, which in 
turn would 

Re: [HACKERS] Tips/advice for implementing integrated RESTful HTTP API

[Dobes Vandermeer]

Hmm yes I am learning that the BG worker system isn't as helpful as I had
hoped due to the single database restriction.

As for a writing a frontend this might be the best solution.

A java frontend would be easy but pointless because the whole point here is
to provide a lightweight access method to the database for environments
that don't have the ability to use the jdbc or libpq libraries.  Deploying
a java setup would be too much trouble.

I do see now that PG uses one worker per connection rather than a worker
pool as I had thought before. So there's nothing already in there to help
me dispatch requests and making my own worker pool that distributes
requests using sockets wouldn't be any better than connecting back using
libpq.

A C frontend using libevent would be easy enough to make and deploy for
this I guess.

But... Maybe nobody really wants this thing anyway, there seem to be some
other options out there already.

Thanks for the feedback.
On Aug 31, 2014 8:46 PM, "Craig Ringer"  wrote:

> On 08/31/2014 12:40 PM, Dobes Vandermeer wrote:
> > 1. Connecting to multiple databases
> >
> > The background workers can apparently only connect to a single database
> > at a time, but I want to expose all the databases via the API.
>
> bgworkers are assigned a database at launch time (if SPI is enabled),
> and this database may not change during the worker's lifetime, same as a
> normal backend.
>
> Sometimes frustrating, but that's how it is.
>
> > I think I could use libpq to connect to PostgreSQL on localhost but this
> > might have weird side-effects in terms of authentication, pid use, stuff
> > like that.
>
> If you're going to do that, why use a bgworker at all?
>
> In general, what do you gain from trying to do this within the database
> server its self, not as an app in front of the DB?
>
> > I could probably manage a pool of dynamic workers (as of 9.4), one per
> > user/database combination or something along those lines.  Even one per
> > request?  Is there some kind of IPC system in place to help shuttle the
> > requests and responses between dynamic workers?  Or do I need to come up
> > with my own?
>
> The dynamic shmem code apparently has some queuing functionality. I
> haven't used it yet.
>
> > It seems like PostgreSQL itself has a way to shuttle requests out to
> > workers, is it possible to tap into that system instead?  Basically some
> > way to send the requests to a PostgreSQL backend from the background
> worker?
>
> It does?
>
> It's not the SPI, that executes work directly within the bgworker,
> making it behave like a normal backend for the purpose of query execution.
>
> > Or perhaps I shouldn't do this as a worker but rather modify PostgreSQL
> > itself and do it in a more integrated/destructive manner?
>
> Or just write a front-end.
>
> The problem you'd have attempting to modify PostgreSQL its self for this
> is that connection dispatch occurs via the postmaster, which is a
> single-threaded process that already needs to do a bit of work to keep
> an eye on how things are running. You don't want it constantly busy
> processing and dispatching millions of tiny HTTP requests. It can't just
> hand a connection off to a back-end immediately after accepting it,
> either; it'd have to read the HTTP headers to determine what database to
> connect to. Then launch a new backend for the connection, which is
> horribly inefficient when doing tiny short-lived connections. The
> postmaster has no concept of a pool of backends (unfortunately, IMO) to
> re-use.
>
> I imagine (it's not something I've investigated, really) that you'd want
> a connection accepter process that watched the listening http request
> socket. It'd hand connections off to dispatcher processes that read the
> message content to get the target DB and dispatch the request to a
> worker backend for the appropriate user/db combo, then collect the
> results and return them on the connection. Hopefully at this point
> you're thinking "that sounds a lot like a connection pool"... because it
> is. An awfully complicated one, probably, as you'd have to manage
> everything using shared memory segments and latches.
>
> In my view it's unwise to try to do this in the DB with PostgreSQL's
> architecture. Hack PgBouncer or PgPool to do what you want. Or write a
> server with Tomcat/Jetty using JAX-RS and PgJDBC and the built in
> connection pool facilities - you won't *believe* how easy it is.
>
> > 3. Parallelism
> >
> > The regular PostgreSQL server can run many queries in parallel
>
> Well, one PostgreSQL instance (postmaster) may have many backends, each
> of which may run queries in series but not in parallel. Any given
> process may only run one query at once.
>
> > but it
> > seems like if I am using SPI I could only run one query at a time - it's
> > not an asynchronous API.
>
> Correct.
>
> > Any help, sage advice, tips, and suggestions how to move forward in
> > these areas would be muchly appreciated!
>
> Don't do it with bg

Re: [HACKERS] On partitioning

[Heikki Linnakangas]

On 09/01/2014 06:59 PM, Tom Lane wrote:

Greg Stark  writes:

On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane  wrote:

Aside from costing planning time, most likely that would forever prevent
us from pushing some types of intelligence about partitioning into the
executor.



How would it affect this calculus if there were partitioned indexes
which were created on the overall table and guaranteed to exist on
each partition that the planner could use -- and then possibly also
per-partition indexes that might exist in addition to those?


That doesn't actually fix the planning-time issue at all.  Either the
planner considers each partition individually to create a custom plan
for it, or it doesn't.


Hmm. Couldn't you plan together all partitions that do have the same 
indexes? In other words, create a custom plan for each group of 
partitions, rather than each partition?


- Heikki



--
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] On partitioning

[Andres Freund]

On 2014-09-01 11:59:37 -0400, Tom Lane wrote:
> Greg Stark  writes:
> > On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane  wrote:
> >> Aside from costing planning time, most likely that would forever prevent
> >> us from pushing some types of intelligence about partitioning into the
> >> executor.
> 
> > How would it affect this calculus if there were partitioned indexes
> > which were created on the overall table and guaranteed to exist on
> > each partition that the planner could use -- and then possibly also
> > per-partition indexes that might exist in addition to those?
> 
> That doesn't actually fix the planning-time issue at all.  Either the
> planner considers each partition individually to create a custom plan
> for it, or it doesn't.

We could have a information about the indexing situation in child
partitions on the toplevel table. I.e. note whether child partitions
have individual indexes. And possibly constraints.

> The "push into executor" idea I was alluding to is that we might invent
> plan constructs like a ModifyTable node that applies to a whole
> inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be
> done at runtime.  You're not going to get a plan structure like that
> if the planner is building a separate plan subtree for each partition.

It doesn't sound impossible to evaluate at plan time whether to use
nodes covering several partitions or use a separate subplan for
individual partitions. We're going to need information which partitions
to scan in those nodes anyway.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] On partitioning

[Greg Stark]

On Mon, Sep 1, 2014 at 4:59 PM, Tom Lane  wrote:
> The "push into executor" idea I was alluding to is that we might invent
> plan constructs like a ModifyTable node that applies to a whole
> inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be
> done at runtime.  You're not going to get a plan structure like that
> if the planner is building a separate plan subtree for each partition.

Well my message was assuming that in that case it would only consider
the partitioned indexes. It would only consider the isolated indexes
if the planner was able to identify a specific partition. That's
probably the only type of query where such indexes are likely to be
useful.


-- 
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] On partitioning

[Andres Freund]

On 2014-08-31 16:03:30 -0400, Tom Lane wrote:
> Another thought about this general topic:
> 
> Alvaro Herrera  writes:
> > ...
> > Allowed actions on a RELKIND_PARTITION:
> > * CREATE INDEX .. ON PARTITION  ON TABLE 
> > ...
> > Still To Be Designed
> > 
> > * Are indexes/constraints inherited from the parent rel?
> 
> I think one of the key design decisions we have to make is whether
> partitions are all constrained to have exactly the same set of indexes.
> If we don't insist on that it will greatly complicate planning compared
> to what we'll get if we do insist on it, because then the planner will
> need to generate a separate customized plan subtree for each partition.
> Aside from costing planning time, most likely that would forever prevent
> us from pushing some types of intelligence about partitioning into the
> executor.

> Now, in the current model, it's up to the user what indexes to create
> on each partition, and sometimes one might feel that maintaining a
> particular index is unnecessary in some partitions.  But the flip side
> of that is it's awfully easy to screw yourself by forgetting to add
> some index when you add a new partition.  So I'm not real sure which
> approach is superior from a purely user-oriented perspective.

I think we're likely to end up with both. In many cases it'll be far
superior from a usability and planning perspective to have indices on
the 'toplevel table' (do we have a good name for that?).

But on the flip side, one of the significant use cases for partitioning
is dealing with historical data. In many cases old data has to be saved
for years but is barely ever queried. It'd be a shame to inflict all
indexes on all partitions for that kind of data. It'd surely be a useful
step to add sane partitioning without that capability, but we shouldn't
base the design on that decision.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] [BUGS] BUG #9652: inet types don't support min/max

[Haribabu Kommi]

On Fri, Aug 29, 2014 at 12:39 PM, Tom Lane  wrote:
> Haribabu Kommi  writes:
>> Thanks for your review. Please find the rebased patch to latest HEAD.
>
> Committed with minor (mostly cosmetic) alterations.

Thanks.

Regards,
Hari Babu
Fujitsu Australia


-- 
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] On partitioning

[Tom Lane]

Greg Stark  writes:
> On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane  wrote:
>> Aside from costing planning time, most likely that would forever prevent
>> us from pushing some types of intelligence about partitioning into the
>> executor.

> How would it affect this calculus if there were partitioned indexes
> which were created on the overall table and guaranteed to exist on
> each partition that the planner could use -- and then possibly also
> per-partition indexes that might exist in addition to those?

That doesn't actually fix the planning-time issue at all.  Either the
planner considers each partition individually to create a custom plan
for it, or it doesn't.

The "push into executor" idea I was alluding to is that we might invent
plan constructs like a ModifyTable node that applies to a whole
inheritance^H^H^Hpartitioning tree and leaves the tuple routing to be
done at runtime.  You're not going to get a plan structure like that
if the planner is building a separate plan subtree for each partition.

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] On partitioning

[Andres Freund]

On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
> It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
> pagenr, tupnr).
> 
> Cross-partition indexes are especially needed if we want to allow putting
> UNIQUE constraints on non-partition-key columns.

I actually don't think this is necessary. I'm pretty sure that you can
build an efficient and correct version of unique constraints with
several underlying indexes in different partitions each. The way
exclusion constraints are implemented imo is a good guide.

I personally think that implementing cross partition indexes has a low
enough cost/benefit ratio that I doubt it's wise to tackle it anytime
soon.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] On partitioning

[Greg Stark]

On Sun, Aug 31, 2014 at 9:03 PM, Tom Lane  wrote:
> Aside from costing planning time, most likely that would forever prevent
> us from pushing some types of intelligence about partitioning into the
> executor.

How would it affect this calculus if there were partitioned indexes
which were created on the overall table and guaranteed to exist on
each partition that the planner could use -- and then possibly also
per-partition indexes that might exist in addition to those? So the
planner could make deductions and leave some intelligence about
partitions to the executor as long as they only depend on partitioned
indexes but might be able to take advantage of a per-partition index
if it's an unusual situation. I'm imagining for example a partitioned
table where only the current partition is read-write and OLTP queries
restrict themselves to working only with the current partition. Having
excluded the other partitions the planner is free to use any of the
indexes liberally.

That said, I think the typical approach to this is to only allow
indexes that are defined for the whole table. If the user wants to
have different indexes for the current time period they would have a
separate table with all the indexes on it that is only moved into the
partitioned table once it's finished being used for for the atypical
queries. Oracle supports "local partitioned indexes" (which are
partitioned like the table) and "global indexes" (which span
partitions) but afaik it doesn't support indexes on only some
partitions.

Furthermore, we have partial indexes. Partial indexes mean you can
always create a partial index on just one partition's range of keys.
The index will exist for all partitions but just be empty for all but
the partitions that matter. The planner can plan based on the partial
index's where clause which would accomplish the same thing, I think.


-- 
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] PL/pgSQL 2

[Tom Lane]

Andres Freund  writes:
> On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
>> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule  
>> wrote:
>>> It bad signal to have two languages plpgsql and plpgsql2. Who will believe
>>> to us so we will continue development of plpgsql?

>> Depends on how you define "development".
>> Bugfixes of plpgsql? Yes, of course.
>> New features? No, but that's a non-issue since we all know it's more
>> or less impossible to introduce new features without breaking
>> compatibility, I think you will agree on that, no?

> Sorry, but that's just plain wrong. There've been plenty of new features
> for plpgsql. You're not very convincing if you use bogus arguments like
> this.

And even more to the point: once plpgsql2 is released, the *exact same*
compatibility arguments will limit further development of it.

Perhaps, if you were very smart and designed a language from scratch
without worrying about whether it looked anything like plpgsql, you
could come up with something that would be easier to extend without
creating compatibility issues.  But that's not what's being proposed here.

What is actually being proposed, AFAICS, is a one-shot fix for a bunch
of unfortunate choices.  That might be worth doing, but let's not fool
ourselves about whether it's one-shot or not.

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

[HACKERS] Patch for psql History Display on MacOSX

[Stepan Rutz]

Hi everbody,

My first mail to this one, so please be mild. I fired up the debugger to get 
this item going, which is also on the Todo List. 

Attached is a very trivial patch as a basis for discussion that at least makes 
\s (show history) work in psql on Macs. Macs uses libedit, which has a 
libreadline interface. 

A short investigation showed that the way psql iterates over the history does 
not work with libedit. I changed the iteration scheme to an index based loop 
(see code and comments), which seemed to be the only working option for both 
readline and libedit. In any case, i have tested and compiled this on MacOX 
10.9.3 and Linux. Windows doesn’t have the pager in the first place. 

As noted in the todo I have made this code pay attention to the pager 
configuration from psql. The odd part is when your history opens in less you 
see the top part rather then the bottom part, but the bottom is just a single 
keystroke away. If pager is disabled history is just printed fine. Please note 
that this didn’t work at all on Mac before. Could this go into 
…./regress/sql/psql.sql at all? I am not sure on that one.

Regards, Stepan







psql_pager_history_libedit_and_readline.patch
Description: Binary data


smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] PL/pgSQL 2

[David G Johnston]

Joel Jacobson-2 wrote
> On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer <

> craig@

> > wrote:
>> Well, the idiom:
>>
>>   EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>>
>> is not lovely. It works, but it's clumsy.
> 
> This is exactly why we need a new language.
> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
> plpgsql2, with the most beautiful syntax we can come up with.
> 
> I guess it's a question if we want to support things like this. If we
> want to, then we also want a new language.

Regardless of how they are ultimately implemented - a new language, PRAGMA
infrastructure, or adding syntax to plpgsql - improving upon the current
6-item ToDo on the Wiki would be a lot more valuable at this point in time. 
I do think better organization than the current ToDo format can be had but
even just moving many of these ideas there would be a start.

The goal isn't to introduce a new language version - that would simply be a
means to achieving an end that is not reasonably achievable in any other
way.

Ideally, for each feature and behavior, we'd be able to provide suggestions
on how it could be implemented in the various cases and the pros/cons of
doing so in each.  If there are enough items with beautiful syntax in
version 2 of the language then that concrete evidence and action plan would
have a much better chance of gaining commiter support than generalities and
theories.

Adding a whole new language to avoid 2 pet-peeves and an introduce a
behavior (syntax to allow multi-row DDL) that is controversial is not likely
to seem worth it to those you are asking to write and maintain this new
language.  I get the goal of the post was to generate ideas but at the same
time the idea generation can occur without you putting forth the conclusion
that a new language is required - conclusions like that are not useful at
the start of a research project (even if that conclusion motivated the
project in the first place).

David J.
 



--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PL-pgSQL-2-tp5817121p5817199.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


-- 
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] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 5:16 PM, Craig Ringer  wrote:
> On 09/01/2014 10:41 PM, Joel Jacobson wrote:
>> This is exactly why we need a new language.
>> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
>> plpgsql2, with the most beautiful syntax we can come up with.
>>
>> I guess it's a question if we want to support things like this. If we
>> want to, then we also want a new language.
>
> Given how much bike shedding occurs around trivial features, can you
> imagine how long that'd take?

I wasn't aware of the expression "bike shedding" so I had to look it up.
It apparently means "spend the majority of its time on relatively
unimportant but easy-to-grasp issues".
If you feel the development of plpgsql falls into this category, that
most time is spent on the smaller unimportant things, isn't that a
clear sign we need plpgsql2, for there to be any hope of progress on
the important things?


-- 
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] PL/pgSQL 2

[Craig Ringer]

On 09/01/2014 10:41 PM, Joel Jacobson wrote:
> This is exactly why we need a new language.
> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
> plpgsql2, with the most beautiful syntax we can come up with.
> 
> I guess it's a question if we want to support things like this. If we
> want to, then we also want a new language.

Given how much bike shedding occurs around trivial features, can you
imagine how long that'd take?

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 4:41 PM, Andres Freund  wrote:
>> I'm just saying it's much less probable you can add new features to
>> plpgsql than to plpgsql2, as you have to take into account the risk of
>> breaking compatibility.
>
> That's just a difference of one release. The release after the set of
> problems is nearly identical.

That's not true. The first release (plpgsql -> plpgsql2) will be a
major release.
After that, we can do minor releases for the following X years, until
we possible need for a new major version.
Each minor release would be guaranteed not to break any backwards compatibility.

plpgsql -> plpgsql2 would be the single giant leap we take into the future.

I think this reasoning is quite compatible with the versioning policy
of the project in general, where we distinguish between major and
minor releases.


-- 
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] psql \watch versus \timing

[Tom Lane]

Michael Paquier  writes:
> I just tested the patch and this feature works as expected if timing
> is on and it displays the individual run time of each query kicked by
> \watch. Note that --echo-hidden does not display the query run during
> each loop and that this is contrary to the behavior in HEAD so it
> breaks backward compatibility, but are there really people relying in
> the existing behavior?

ISTM that's an anti-feature anyway, and changing that behavior is a
good thing.

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] PL/pgSQL 2

[Pavel Stehule]

2014-09-01 16:41 GMT+02:00 Joel Jacobson :

> On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer 
> wrote:
> > Well, the idiom:
> >
> >   EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
> >
> > is not lovely. It works, but it's clumsy.
>
> This is exactly why we need a new language.
> All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
> plpgsql2, with the most beautiful syntax we can come up with.
>
> I guess it's a question if we want to support things like this. If we
> want to, then we also want a new language.
>

when you fix it, then you designed totally new language with thin relation
to plpgsql

Pavel

Re: [HACKERS] PL/pgSQL 2

[Pavel Stehule]

2014-09-01 16:39 GMT+02:00 Hannu Krosing :

> On 09/01/2014 03:45 PM, Craig Ringer wrote:
> > On 09/01/2014 05:04 PM, Joel Jacobson wrote:
> >> Just like with plpgsql, once released, plpgsql2 cannot break
> >> compatibility with future versions, so we only have one chance to
> >> carefully think though what we would like to change in the language.
> > You're not proposing to copy plpgsql's runtime though, right? Just add
> > conditional paths where v1 and v2 differ.
> >
> > Personally I'd rather look at adding language version pragmas or a new
> > function attribute, but that's mostly bikeshedding.
> >
> >> From the top of my head, these are Things I personally would want to see
> >> in plpgsql2:
> > - Accept RECORD input, dynamic access to fields of records without
> >   resorting to hstore hacks. This is certainly my #1.
> >
> Also, an easy way to tell pl/pgsql to *not* cache plans without
> resorting to EXECUT'ins trings would nice
>

It is good request

maybe

#option noplancache

or

SELECT /* NOPLANCACHE */

Pavel



>
> Cheers
> Hannu
>
> --
> Hannu Krosing
> PostgreSQL Consultant
> Performance, Scalability and High Availability
> 2ndQuadrant Nordic OÜ
>
>
>
> --
> 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] PL/pgSQL 2

[Heikki Linnakangas]

On 09/01/2014 05:41 PM, Joel Jacobson wrote:

On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer  wrote:

Well, the idiom:

   EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;

is not lovely. It works, but it's clumsy.


This is exactly why we need a new language.


We could certainly improve that syntax in PL/pgSQL. No need to start 
from scratch for that..


Got a suggestion what the syntax should look like?

- Heikki



--
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] PL/pgSQL 2

[Andres Freund]

On 2014-09-01 16:29:18 +0200, Joel Jacobson wrote:
> On Mon, Sep 1, 2014 at 3:25 PM, Andres Freund  wrote:
> > On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
> >> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule  
> >> wrote:
> >> > It bad signal to have two languages plpgsql and plpgsql2. Who will 
> >> > believe
> >> > to us so we will continue development of plpgsql?
> >>
> >> Depends on how you define "development".
> >> Bugfixes of plpgsql? Yes, of course.
> >> New features? No, but that's a non-issue since we all know it's more
> >> or less impossible to introduce new features without breaking
> >> compatibility, I think you will agree on that, no?
> >
> > Sorry, but that's just plain wrong. There've been plenty of new features
> > for plpgsql. You're not very convincing if you use bogus arguments like
> > this.
> 
> You misunderstood, what I said was it's "more or less impossible",
> that's different from "impossible".

It's still bullshit. It's very hard to change *existing* semantics and
thus existing code. Which isn't something plpgsql specifically has
difficulties with. It's a far more general concern affecting pretty much
all released software; more so software with a large existing user
base. Remember all the flak postgres got for the cast issues with 8.3?

It's about has hard to add additional features, that don't break
existing code, to plpgsql as to most of the rest of postgres. Which
isn't to say it's easy. Believe me, I know that.

> I'm just saying it's much less probable you can add new features to
> plpgsql than to plpgsql2, as you have to take into account the risk of
> breaking compatibility.

That's just a difference of one release. The release after the set of
problems is nearly identical.

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 4:26 PM, Craig Ringer  wrote:
> Well, the idiom:
>
>   EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>
> is not lovely. It works, but it's clumsy.

This is exactly why we need a new language.
All the clumsy stuff we cannot fix in plpgsql, can easily be fixed in
plpgsql2, with the most beautiful syntax we can come up with.

I guess it's a question if we want to support things like this. If we
want to, then we also want a new language.


-- 
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] PL/pgSQL 2

[Hannu Krosing]

On 09/01/2014 03:45 PM, Craig Ringer wrote:
> On 09/01/2014 05:04 PM, Joel Jacobson wrote:
>> Just like with plpgsql, once released, plpgsql2 cannot break
>> compatibility with future versions, so we only have one chance to
>> carefully think though what we would like to change in the language.
> You're not proposing to copy plpgsql's runtime though, right? Just add
> conditional paths where v1 and v2 differ.
>
> Personally I'd rather look at adding language version pragmas or a new
> function attribute, but that's mostly bikeshedding.
>
>> From the top of my head, these are Things I personally would want to see
>> in plpgsql2:
> - Accept RECORD input, dynamic access to fields of records without
>   resorting to hstore hacks. This is certainly my #1.
>
Also, an easy way to tell pl/pgsql to *not* cache plans without
resorting to EXECUT'ins trings would nice

Cheers
Hannu

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] PL/pgSQL 2

[Pavel Stehule]

2014-09-01 16:26 GMT+02:00 Craig Ringer :

> On 09/01/2014 10:24 PM, Andres Freund wrote:
> > I know of format(), but it doesn't allow you to pass parameters as
> > actual query variables unfortunately.
> > I'm wondering if there's a way to marry USING and format()...
>
> Well, the idiom:
>
>   EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;
>
> is not lovely. It works, but it's clumsy.
>

It is not intuitive, but It is well descriptive about PL/pgSQL. I cannot to
imagine some different - it is mix of two independent worlds. And at end it
is elegant.

If we start new language from scratch, then we don't need to introduce
concept of embedded SQL or dynamic SQL.

But why? If it is too clumsy for you, use PLPerl or PLPython. Everytime
will be problem how to merge two different namespaces together.


>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [HACKERS] PL/pgSQL 2

[Joel Jacobson]

+1

I use underscore for *all* variables and input parameters in all
functions. Making that a requirement in plpgsql2 wouldn't break any of
my code.

On Mon, Sep 1, 2014 at 3:52 PM, Craig Ringer  wrote:
> On 09/01/2014 05:04 PM, Joel Jacobson wrote:
>> From the top of my head, these are Things I personally would want to see
>> in plpgsql2:
>
> Oh, also, I'd *love* to improve how non-plannable statements with
> PL/PgSQL variable subsitutions behave.
>
> *I* understand why the following is wrong:
>
> DO
> $$
> DECLARE
>  tablename text;
> BEGIN
> tablename := 'sometable';
> DROP TABLE tablename;
> END;
> $$;
>
> and produces:
>
> ERROR:  table "tablename" does not exist
> CONTEXT:  SQL statement "DROP TABLE tablename"
> PL/pgSQL function inline_code_block line 6 at SQL statement
>
>
> but going by the number of questions I see about this, and similar
> issues with ALTER USER and so on, it's not obvious to new users.
>
> It's not clear how to make this friendly without making it dangerous
> though. If you have:
>
> DROP TABLE x;
>
> then someone declares a variable
>
> x := 'customers';
>
> you don't want to suddenly be issuing a
>
> DROP TABLE customers;
>
> ... which is why I suspect this might need to be not 100% backward
> compatible, perhaps requiring a variable-marker prefix.
>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Joel Jacobson]

On Mon, Sep 1, 2014 at 3:25 PM, Andres Freund  wrote:
> On 2014-09-01 15:19:41 +0200, Joel Jacobson wrote:
>> On Mon, Sep 1, 2014 at 2:53 PM, Pavel Stehule  
>> wrote:
>> > It bad signal to have two languages plpgsql and plpgsql2. Who will believe
>> > to us so we will continue development of plpgsql?
>>
>> Depends on how you define "development".
>> Bugfixes of plpgsql? Yes, of course.
>> New features? No, but that's a non-issue since we all know it's more
>> or less impossible to introduce new features without breaking
>> compatibility, I think you will agree on that, no?
>
> Sorry, but that's just plain wrong. There've been plenty of new features
> for plpgsql. You're not very convincing if you use bogus arguments like
> this.

You misunderstood, what I said was it's "more or less impossible",
that's different from "impossible".
*If* a feature can be added to plpgsql, it sure can be added to
plpgsql2 too, so of course it should be added to both.
I'm just saying it's much less probable you can add new features to
plpgsql than to plpgsql2, as you have to take into account the risk of
breaking compatibility.


-- 
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] PL/pgSQL 2

[Craig Ringer]

On 09/01/2014 10:24 PM, Andres Freund wrote:
> I know of format(), but it doesn't allow you to pass parameters as
> actual query variables unfortunately.
> I'm wondering if there's a way to marry USING and format()...

Well, the idiom:

  EXECUTE format("SELECT %I FROM %I WHERE $1", col, tbl) USING val;

is not lovely. It works, but it's clumsy.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Andres Freund]

On 2014-09-01 22:20:37 +0800, Craig Ringer wrote:
> On 09/01/2014 10:17 PM, Andres Freund wrote:
> > Imo this is still something that's more dynamic SQL (i.e. EXECUTE's
> > remit) than something that shouldn't be doable implicitly. So perhaps
> > the solution is to extend EXECUTE to allow specifying tablenames as
> > variables more conveniently?
> 
> With format(...) it's pretty easy, really.

I know of format(), but it doesn't allow you to pass parameters as
actual query variables unfortunately.
I'm wondering if there's a way to marry USING and format()...

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Pavel Stehule]

2014-09-01 16:18 GMT+02:00 Craig Ringer :

> On 09/01/2014 10:11 PM, Pavel Stehule wrote:
> >
> > It can be solution, but I dislike it .. It increase a language
> > complexity .. vars with or without prefix .. and more,  hidden dynamic
> SQL
> >
> > Nothing what I like  - I have a mental barrier to this concept.
>
> Yeah - the question is whether it's better than using EXECUTE.
>
> I'm not convinced it is, since it doesn't make things any more
> discoverable for new users, and existing users already know how to do it
> right.
>
> It'd only really improve things if PL/PgSQL had started off using
> $variable notation, or something that wasn't otherwise legal as an
> identifier.
>

yes .. dynamic SQL is terrible

but it is good to understand to this concept quickly - because it is core
of integration SQL to PL/pgSQL.


>
> I don't love how it works now, but I don't have a better answer really.
>

I have same opinion. It is not ideal now, but I don't any better ideal


>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>

Re: [HACKERS] PL/pgSQL 2

[Craig Ringer]

On 09/01/2014 10:17 PM, Andres Freund wrote:
> Imo this is still something that's more dynamic SQL (i.e. EXECUTE's
> remit) than something that shouldn't be doable implicitly. So perhaps
> the solution is to extend EXECUTE to allow specifying tablenames as
> variables more conveniently?

With format(...) it's pretty easy, really.

Perhaps just changing the docs to remove all the quote_ident based
examples in favour of format(...) would be enough.

Pavel's points are pretty good - what exists now isn't ideal from a
usability and friendliness PoV, but changing it would require making a
big mess elsewhere that's not worth doing.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Craig Ringer]

On 09/01/2014 10:11 PM, Pavel Stehule wrote:
> 
> It can be solution, but I dislike it .. It increase a language
> complexity .. vars with or without prefix .. and more,  hidden dynamic SQL
> 
> Nothing what I like  - I have a mental barrier to this concept.

Yeah - the question is whether it's better than using EXECUTE.

I'm not convinced it is, since it doesn't make things any more
discoverable for new users, and existing users already know how to do it
right.

It'd only really improve things if PL/PgSQL had started off using
$variable notation, or something that wasn't otherwise legal as an
identifier.

I don't love how it works now, but I don't have a better answer really.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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] PL/pgSQL 2

[Andres Freund]

On 2014-09-01 22:01:33 +0800, Craig Ringer wrote:
> On 09/01/2014 09:58 PM, Pavel Stehule wrote:
> > 
> > It is in ToDo - allow parametrization for COMMANDs.
> > 
> > But this is one point, when I am not sure if we would it. Now -
> > situation is very simply. Variables should not be used as table or
> > column name. With your proposal, the situation will by much more
> > complex, and probably variables should be used in SELECT * FROM x; but
> > it can be ambiguous SELECT x FROM table ... so maybe better to don't
> > allow it
> 
> We would have to define a specific parameter marker, as used by things
> like JDBC.
> 
>  SELECT x FROM ?tablename
> 
>  DROP TABLE ?tablename
> 
> or whatever your favourite place-holder syntax is.

Imo this is still something that's more dynamic SQL (i.e. EXECUTE's
remit) than something that shouldn't be doable implicitly. So perhaps
the solution is to extend EXECUTE to allow specifying tablenames as
variables more conveniently?

Greetings,

Andres Freund

-- 
 Andres Freund http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, 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