Re: [HACKERS] PL/PgSQL: EXIT USING ROLLBACK

[Joel Jacobson]

> On 3 sep 2014, at 16:20, Robert Haas  wrote:
>
>> On Mon, Sep 1, 2014 at 5:08 AM, Joel Jacobson  wrote:
>>> On Sat, Jul 26, 2014 at 8:39 PM, Tom Lane  wrote:
>>> Basically my point is that this just seems like inventing another way to
>>> do what one can already do with RAISE, and it doesn't have much redeeming
>>> social value to justify the cognitive load of inventing another construct.
>>
>> The main difference is with RAISE EXCEPTION 'OK'; you cannot know if
>> it was *your* line of code which throw the 'OK'-exception or if it
>> came from some other function which was called in the block of code.
>
> The real problem here is that if you're using PL/pgsql exceptions for
> control-flow reasons, you are taking a huge performance hit for that
> notational convenience.  I do agree that the syntax of PL/pgsql is
> clunky and maybe we should fix that anyway, but I honestly can't
> imagine too many people actually wanting to do this once they realize
> what it does to the run time of their procedure (and in some cases,
> the XID-consumption rate of their database).

Exceptions in plpgsql is indeed an exception itself :-)

There are a few use cases when they are crucial though, I would say I
use this code pattern in 0.1% of all functions, but still, when I need
this, it gets ugly.

Glad to hear you might consider the idea of fixing this.

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company


-- 
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] ODBC Driver performance comparison

[Heikki Linnakangas]

I just replied to this on pgsql-odbc mailing list. Since we're talking 
about client performance, please keep the discussion there.


On 09/05/2014 08:54 AM, Vladimir Romanov wrote:

Hello all!
I do some test with ODBC driver for PosgreSql, TimesTen & MySQL. I compare
performance on very simple request. Database always located on same PC as
test application. Test PC - Lenovo T500, Cnetos 6.5 64, 8 Gb RAM, SSD.
I found what PostgreSql ODBC driver is slowest in comparison.
IMHO problems related to protocol used. I can't use SHM connection to
server or even UNIX socket.

perftool report - http://freepcrf.com/files/db_test_pq.pdf
chart 1 (w/o timesten)  - http://freepcrf.com/files/drv_comp1.png
chart 2 - http://freepcrf.com/files/drv_comp2.png
test source - https://github.com/vvromanov/db_test




- 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] Escaping from blocked send() reprised.

[Kyotaro HORIGUCHI]

Hello,

> > - This patch introduced redundant socket emulation for win32
> >backend but win32 bare socket for Port is already nonblocking
> >as described so it donsn't seem to be a serious problem on
> >performance. Addition to it, since I don't know the reason why
> >win32/socket.c provides the blocking-mode socket emulation, I
> >decided to preserve win32/socket.c to have blocking socket
> >emulation. Possibly it can be removed.
> 
> On Windows, the backend has an emulation layer for POSIX signals,
> which uses threads and Windows events. The reason win32/socket.c
> always uses non-blocking mode internally is that it needs to wait for
> the socket to become readable/writeable, and for the signal-emulation
> event, at the same time. So no, we can't remove it.

I see, thank you.

> The approach taken in the first patch seems sensible. I changed it to
> not use FD_SET, though. A custom array seems better, that way we don't
> need the pgwin32_nonblockset_init() call, we can just use initialize
> the variable. It's a little bit more code, but it's well-contained in
> win32/socket.c. Please take a look, to double-check that I didn't
> screw up.

Thank you. I felt a bit qualm to abusing fd_set. A bit more code
is not a problem.

I had close look on your patch.

Both 'nonblocking' and 'noblock' are appears in function names,
pgwin32_set_socket_block/noblock/is_nonblocking(). I prefer
nonblocking/blocking pair but I'm satisfied they are in uniform
style anyway. (Though I also didn't so ;p)

pgwin32_set_socket_block() leaves garbage in
nonblocking_sockets[] but it's no problem practically. You also
removed blocking'ize(?) code but I agree that it is correct
because fds of nonclosed socket won't be reused anyway.

pg_set_block/noblock() made me laugh. Yes you're correct. Sorry
for the bronken (but workable) code.

After all, the patch looks pretty good.
I'll continue to fit the another patch onto this.

regards,

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

[Pavel Stehule]

Thank you

Regards

Pavel


2014-09-05 8:29 GMT+02:00 Jeevan Chalke :

>
>
>
> On Thu, Sep 4, 2014 at 11:41 AM, Pavel Stehule 
> wrote:
>
>> I am sory
>>
>> too much patches
>>
>
> :)
>
> Patch looks good to me.
> Marking "Ready for Committer".
>
> Thanks
>
>
>>
>> Regards
>>
>> Pavel
>>
>
>
> --
> Jeevan B Chalke
> Principal Software Engineer, Product Development
> EnterpriseDB Corporation
> The Enterprise PostgreSQL Company
>
>

Re: [HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors

[Jeevan Chalke]

On Thu, Sep 4, 2014 at 11:41 AM, Pavel Stehule 
wrote:

> I am sory
>
> too much patches
>

:)

Patch looks good to me.
Marking "Ready for Committer".

Thanks


>
> Regards
>
> Pavel
>


-- 
Jeevan B Chalke
Principal Software Engineer, Product Development
EnterpriseDB Corporation
The Enterprise PostgreSQL Company

[HACKERS] proposal: plpgsql - Assert statement

[Pavel Stehule]

Hello

Assert is usually implemented as custom functions and used via PERFORM
statement now

-- usual current solution
PERFORM Assert(some expression)

I would to implement Assert as plpgsql internal statement due bigger
possibilities to design syntax and internal implementation now and in
future. More - as plpgsql statement should be compatible with any current
code - because there should not be collision between SQL and PLpgSQL space.
So this design doesn't break any current code.

I propose following syntax with following ecosystem:

ASSERT [ NOTICE, WARNING, >>EXCEPTION<< ]
  [ string expression or literal - explicit message ]
  [ USING clause - same as RAISE stmt (possible in future ) ]
  ( ROW_COUNT ( = | <> ) ( 1 | 0 ) |
  ( QUERY some query should not be empty ) |
  ( CHECK some expression should be true )
  ( IS NOT NULL expression should not be null )

Every variant (ROW_COUNT, QUERY, CHECK, IS NOT NULL) has own default message

These asserts can be controlled by set of options (by default asserts are
enabled):

#option asserts_disable
#option asserts_disable_notice .. don't check thin asserts
#option asserts_not_stop ..  raise warning instead exception

some examples:

UPDATE tab SET c = 1 WHERE pk = somevar;
ASSERT ROW_COUNT = 1; -- knows what is previous DML or Dynamic DML

ASSERT CHECK a < 100;

ASSERT IS NOT NULL pk;

ASSERT QUERY SELECT id FROM tab WHERE x = 1;

ASSERT CHECK 2 = (SELECT count(*) FROM tab WHERE x = 1);

ASSERT WARNING "data are there" QUERY SELECT ...

Shorter variant should to work

CREATE OR REPLACE FUNCTION assert(boolean)
RETURNS void AS $$
BEGIN
  ASSERT CHECK $1;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION assert(boolean, text)
RETURNS void AS $$
BEGIN
  ASSERT $1 CHECK $2;
END;
$$ LANGUAGE plpgsql;

Usage:

PERFORM assert(a <> 10);
PERFORM assert(a <> 10, "a should be 10");

Comments, notices?

Regards

Pavel

This design should not break any current solution, it allows a static
analyses, and it doesn't close a door for future enhancing.

[HACKERS] ODBC Driver performance comparison

[Vladimir Romanov]

Hello all!
I do some test with ODBC driver for PosgreSql, TimesTen & MySQL. I compare
performance on very simple request. Database always located on same PC as
test application. Test PC - Lenovo T500, Cnetos 6.5 64, 8 Gb RAM, SSD.
I found what PostgreSql ODBC driver is slowest in comparison.
IMHO problems related to protocol used. I can't use SHM connection to
server or even UNIX socket.

perftool report - http://freepcrf.com/files/db_test_pq.pdf
chart 1 (w/o timesten)  - http://freepcrf.com/files/drv_comp1.png
chart 2 - http://freepcrf.com/files/drv_comp2.png
test source - https://github.com/vvromanov/db_test


-- 
Vladimir Romanov

Re: [HACKERS] pg_receivexlog --status-interval add fsync feedback

[furuyao]

> > Thanks for the review!
> >
> > I understand the attention message wasn't appropriate.
> >
> > To report the write location, even If you do not specify a replication
> slot.
> > So the fix only appended messages.
> >
> > There was a description of the flush location section of '-S' option,
> > but I intended to catch eye more and added a message.
> >
> > Is it better to make specification of the -S option indispensable?
> 
> The patch cannot be applied to HEAD cleanly. Could you update the patch?

Thank you for pointing out.
Updated the patch.

Regards,

--
Furuya Osamu


pg_receivexlog-fsync-feedback-v5.patch
Description: pg_receivexlog-fsync-feedback-v5.patch

-- 
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] A mechanism securing web applications in DBMS

[Laurence Rowe]

> > 2.1 The authentication problem
> > We address the authentication problem by requiring developers to
> > define an authentication function in the DBMS. This function is
> > invoked whenever an application-level user logs in. An authentication
> > function contains the authentication logic in the server-side code.
> > Besides, it also caches some attributes of the current
> > application-level user, which will be checked by the fine-grained
> > access control mechanism.
>
> This is one approach which can be used, though PG has a reasonably
> complex authentication system which applications can leverage (consider
> Kerberos proxying and client-side certificates), rather than having the
> application independently develop an authentication system.  Still, if
> the application is to be responsible for the authentication, then a
> simple 'SET ROLE' can be done in PG to switch to the context of an
> individual user.
>
[...]

> > 3.1 Authentication Function
> >
> > The major challenge of this part is how to cache users’ authentication
> > information. In our prototype implementation, an authentication table
> > is actually a temporary table. But it makes our GRANT-WHERE statements
> > also temporary since they refer to the authentication table. Since
> > every session has its own authentication table and all the
> > authentication tables share the same schema, the global temporary
> > table [2] would be a perfect fit for caching  users’ authentication
> > information. Also, it will not make GRANT-WHERE statements temporary
> > anymore.
>
> This certainly deserves further discussion.  What is the reason that a
> cacheing mechanism into a temporary table is necessary..?  Are these
> session identifiers to keep track of already-authenticated users?  How
> does that play with a connection pooler (which is a very real
> consideration when thinking about anything web-scale).
>

Absolutely, support for connection pooling is vital for performant web
applications. The problem I see with Kerberos and client side certificates
is that they are only authenticated on the connection level. It would be
helpful if some mechanism existed by which an application could pass a
signed ticket to the database so that the web application user does not
need to be granted privileges to become any user. While Kerberos offers
ultimate flexibility it is rather intimidating. Supporting something a
little simpler would be very helpful.

The scenario I would like to see supported is the following.

1. Web authentication is centralized with users seeing a single log-in
screen. The log-in server issues signed tickets, setting it as a cookie. A
ticket asserts the identity of the user along with a number of additional
principals /groups / roles which are currently authorized (e.g. two-factor
authentication might be required to be issued a delete-permanently
principal.)

2. Web application servers verify a user's ticket by checking the signature
against the login server's public key or in simpler setups with a shared
secret. The ticket has a timeout (or maybe each assertion has a timeout)
and a web application checks it is still valid.

3. In making a database request, the web application passes the ticket down
to the database. Only with this ticket is it able to identify with this
user's id, and only with the group / role / principal assertions is it
granted the additional user roles.

For Apache, mod_auth_tkt and mod_auth_pubtkt are perhaps the simplest
implementations of this concept (Pubcookie/cosign/webauth are more
complex.) As verifying a ticket is cheap, complications around caching can
usually be avoided.

For Postgres, I could imagine the web application interacting with it like
so during the request:

BEGIN;
SELECT
my_auth_function('userid=foo;groups=bar1,bar2;timeout=123456789;signature=abcd1234abcd1234abcd1234abcd1234');
...
COMMIT;

Here my_auth_function would validate the ticket and would need to be able
to do two things not currently possible with a SECURITY DEFINER function:

1. Call SET SESSION AUTHORIZATION / SET ROLE to become a user the
connection user is not otherwise allowed to become.

2. Dynamically set which roles are 'inherited' by the user it is becoming.


Laurence

Re: [HACKERS] Scaling shared buffer eviction

[Mark Kirkwood]

On 04/09/14 14:42, Amit Kapila wrote:

On Thu, Sep 4, 2014 at 8:00 AM, Mark Kirkwood 
wrote:



Hi Amit,

Results look pretty good. Does it help in the read-write case too?


Last time I ran the tpc-b test of pgbench (results of which are
posted earlier in this thread), there doesn't seem to be any major
gain for that, however for cases where read is predominant, you
might see better gains.

I am again planing to take that data in next few days.



FWIW below are some test results on the 60 core beast with this patch 
applied to 9.4. I'll need to do more runs to iron out the variation, but 
it looks like the patch helps the standard (write heavy) pgbench 
workload a little, and clearly helps the read only case.



4x E7-4890 15 cores each.
1 TB ram
16x Toshiba PX02SS SATA SSD
4x Samsung NVMe XS1715 PCIe SSD

Ubuntu 14.04  (Linux 3.13)
Postgres 9.4 beta2
+ buffer eviction patch v5

Pgbench

scale 2000

Non default params:

max_connections = 400;
shared_buffers = "10GB";
maintenance_work_mem = "1GB";
effective_io_concurrency = 10;
wal_buffers = "256MB";
checkpoint_segments = 1920;
checkpoint_completion_target = 0.8;
ssl = 'off';
wal_sync_method = 'open_datasync';

read write

elapsed 600s

Clients  | tps   | tps (unpatched)
-+---+
  6  |  8279 |  8328
  12 | 16260 | 16381
  24 | 23639 | 23451
  48 | 31430 | 31004
  96 | 38516 | 34777
 192 | 33535 | 32443
 384 | 27978 | 25068
 384 | 30589 | 28798


read only

elapsed 300s

Clients  | tps| tps (unpatched)
-++
  6  |  57654 |  57255
  12 | 111361 | 112360
  24 | 220304 | 187967
  48 | 384567 | 230961
  96 | 380309 | 241947
 192 | 330865 | 214570
 384 | 315516 | 207548


Regards

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] pg_receivexlog --status-interval add fsync feedback

[Fujii Masao]

On Fri, Aug 22, 2014 at 1:35 PM,   wrote:
>> Thank you for updating the patch.
>> I reviewed the patch.
>>
>> First of all, I think that we should not append the above message to
>> section of '-r' option.
>> (Or these message might not be needed at all) Whether flush location in
>> feedback message is valid,  is not depend on '-r' option.
>>
>> If we use '-r' option and 'S' option (i.g., replication slot) then
>> pg_receivexlog informs valid flush location to primary server at the same
>> time as doing fsync.
>> But,  if we don't specify replication slot then the flush location in
>> feedback message always invalid.
>> So I think Fujii-san pointed out that sending of invalid flush location
>> is not needed if pg_receivexlog does not use replication slot.
>
> Thanks for the review!
>
> I understand the attention message wasn't appropriate.
>
> To report the write location, even If you do not specify a replication slot.
> So the fix only appended messages.
>
> There was a description of the flush location section of '-S' option,
> but I intended to catch eye more and added a message.
>
> Is it better to make specification of the -S option indispensable?

The patch cannot be applied to HEAD cleanly. Could you update the patch?

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] jsonb format is pessimal for toast compression

[Jan Wieck]

On 08/08/2014 11:18 AM, Tom Lane wrote:

Andrew Dunstan  writes:

On 08/07/2014 11:17 PM, Tom Lane wrote:

I looked into the issue reported in bug #11109.  The problem appears to be
that jsonb's on-disk format is designed in such a way that the leading
portion of any JSON array or object will be fairly incompressible, because
it consists mostly of a strictly-increasing series of integer offsets.



Ouch.



Back when this structure was first presented at pgCon 2013, I wondered
if we shouldn't extract the strings into a dictionary, because of key
repetition, and convinced myself that this shouldn't be necessary
because in significant cases TOAST would take care of it.


That's not really the issue here, I think.  The problem is that a
relatively minor aspect of the representation, namely the choice to store
a series of offsets rather than a series of lengths, produces
nonrepetitive data even when the original input is repetitive.


This is only because the input data was exact copies of the same strings 
over and over again. PGLZ can very well compress slightly less identical 
strings of varying lengths too. Not as well, but well enough. But I 
suspect such input data would make it fail again, even with lengths.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] jsonb format is pessimal for toast compression

[Jan Wieck]

On 08/08/2014 10:21 AM, Andrew Dunstan wrote:


On 08/07/2014 11:17 PM, Tom Lane wrote:

I looked into the issue reported in bug #11109.  The problem appears to be
that jsonb's on-disk format is designed in such a way that the leading
portion of any JSON array or object will be fairly incompressible, because
it consists mostly of a strictly-increasing series of integer offsets.
This interacts poorly with the code in pglz_compress() that gives up if
it's found nothing compressible in the first first_success_by bytes of a
value-to-be-compressed.  (first_success_by is 1024 in the default set of
compression parameters.)


[snip]


There is plenty of compressible data once we get into the repetitive
strings in the payload part --- but that starts at offset 944, and up to
that point there is nothing that pg_lzcompress can get a handle on.  There
are, by definition, no sequences of 4 or more repeated bytes in that area.
I think in principle pg_lzcompress could decide to compress the 3-byte
sequences consisting of the high-order 24 bits of each offset; but it
doesn't choose to do so, probably because of the way its lookup hash table
works:

  * pglz_hist_idx -
  *
  * Computes the history table slot for the lookup by the next 4
  * characters in the input.
  *
  * NB: because we use the next 4 characters, we are not guaranteed to
  * find 3-character matches; they very possibly will be in the wrong
  * hash list.  This seems an acceptable tradeoff for spreading out the
  * hash keys more.

For jsonb header data, the "next 4 characters" are *always* different, so
only a chance hash collision can result in a match.  There is therefore a
pretty good chance that no compression will occur before it gives up
because of first_success_by.

I'm not sure if there is any easy fix for this.  We could possibly change
the default first_success_by value, but I think that'd just be postponing
the problem to larger jsonb objects/arrays, and it would hurt performance
for genuinely incompressible data.  A somewhat painful, but not yet
out-of-the-question, alternative is to change the jsonb on-disk
representation.  Perhaps the JEntry array could be defined as containing
element lengths instead of element ending offsets.  Not sure though if
that would break binary searching for JSON object keys.





Ouch.

Back when this structure was first presented at pgCon 2013, I wondered
if we shouldn't extract the strings into a dictionary, because of key
repetition, and convinced myself that this shouldn't be necessary
because in significant cases TOAST would take care of it.

Maybe we should have pglz_compress() look at the *last* 1024 bytes if it
can't find anything worth compressing in the first, for values larger
than a certain size.

It's worth noting that this is a fairly pathological case. AIUI the
example you constructed has an array with 100k string elements. I don't
think that's typical. So I suspect that unless I've misunderstood the
statement of the problem we're going to find that almost all the jsonb
we will be storing is still compressible.


I also think that a substantial part of the problem of coming up with a 
"representative" data sample is because the size of the incompressible 
data at the beginning is somewhat tied to the overall size of the datum 
itself. This may or may not be true in any particular use case, but as a 
general rule of thumb I would assume that the larger the JSONB document, 
the larger the offset array at the beginning.


Would changing 1024 to a fraction of the datum length for the time being 
give us enough room to come up with a proper solution for 9.5?



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] jsonb format is pessimal for toast compression

[Jan Wieck]

On 08/12/2014 10:58 AM, Robert Haas wrote:

What would really be ideal here is if the JSON code could inform the
toast compression code "this many initial bytes are likely
incompressible, just pass them through without trying, and then start
compressing at byte N", where N is the byte following the TOC.  But I
don't know that there's a reasonable way to implement that.



Sorry, being late for the party.

Anyhow, this strikes me as a good basic direction of thought. But I 
think we should put the burden on the data type, not on toast. To do 
that data types could have an optional toast_hint_values() function, 
which the toast code can call with the actual datum at hand and its 
default parameter array. The hint values function then can modify that 
parameter array, telling toast how much to skip, how hard to try (or not 
at all) and so on. A data type specific function should know much better 
how to figure out how compressible a particular datum may be.


Certainly nothing for 9.4, but it might require changing the toast API 
in a different way than just handing it an oid and hard-coding the 
JASONBOID case into toast for 9.4. If we are going to change the API, we 
might as well do it right.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] A mechanism securing web applications in DBMS

[Stephen Frost]

Zhaomo,

* Zhaomo Yang (zhy...@cs.ucsd.edu) wrote:
> I am a graduate student from UC San Diego. My adviser, Dr. Kirill
> Levchenko, and I have been working on a web/DB security project for
> the last few months. Since fine-grained access control in DBMS is part
> of our project and the PostgreSQL community is also working on it now,
> we would like to exchange some ideas on the issue with you.

Fantastic!  Very interested.

> 1. Background
> Nowadays people entrust more and more sensitive data to web
> applications, while security vulnerabilities are common in these
> applications. The normal structure of web applications consists of
> three tiers: client-side code, server-side code and a database. In
> server-side code a database user representing all the
> application-level users interacts with the database with full
> privileges. Since currently database built-in access control is too
> coarse, no access control mechanism is used in most of web
> applications. This situation is not ideal since a malicious
> application user can tamper with other users’ data by exploiting
> vulnerabilities in the application, and if the application is
> completely under the malicious user’s control so is the database.

Agreed- we are certainly working to improve that situation, though
consideration must also be given to how our catalogs are structured and
that they are unlikely to support "web-scale" numbers of individual
roles as currently implemented.  We're in a better spot than we were a
few years ago (we used to have flat files for roles...), but we lack any
partitioning capability in the catalogs today.

It's entirely possible that, in some situations, the inability of PG to
support the number of roles has dwarfed the permission granularity
concern.  Even today there are mechanisms available to limit what an
individual role can view, add, update, or delete (though they are less
than ideal, rather cludgy to work with and could be better).

> 2. Our Mechanism.
> In order to fix the problem, we believe there are two underlying
> problems to be addressed: authenticating application-level users to
> DBMS and fine-grained access control in the DBMS. Note that these two
> problems are related. Without authenticating application-level users
> to the DBMS, can the DBMS not know which application-level user it is
> actually interacting with, thus the DBMS cannot apply fine-grained
> access control policy correctly.

Agreed- PG needs to know the user to be able to get to the level of
returning only what that individual user is allowed to see.

> 2.1 The authentication problem
> We address the authentication problem by requiring developers to
> define an authentication function in the DBMS. This function is
> invoked whenever an application-level user logs in. An authentication
> function contains the authentication logic in the server-side code.
> Besides, it also caches some attributes of the current
> application-level user, which will be checked by the fine-grained
> access control mechanism.

This is one approach which can be used, though PG has a reasonably
complex authentication system which applications can leverage (consider
Kerberos proxying and client-side certificates), rather than having the
application independently develop an authentication system.  Still, if
the application is to be responsible for the authentication, then a
simple 'SET ROLE' can be done in PG to switch to the context of an
individual user.

> In our implementation, we added a new type of function called
> authentication function. Developers are supposed to use CREATE
> AUTHENTICATION FUNCTION command to define authentication function,
> which contains the application authentication logic and must be
> defined to return table rows. When the authentication function is
> invoked, the return values are cached in a temp table, called the
> authentication table,  with the same name as the authentication
> function. The authentication table is available to access control
> statements (GRANT-WHERE statements).

It'd be great if you could explain the actual difference, in PG, between
these AUTHENTICATION functions and regular functions (or perhaps
security definer functions), which can also create tables and return
rows.

> 2.2 The fined-grained access control problem
> 
> We address the fined-grained access control problem by using
> GRANT-WHERE statements from [1]. Concretely, the syntax of GRANT-WHERE
> statements is
> 
>   GRANT  [, ...] ON  TO  [, ...]
>   USING  [, ...]
>   WHERE ;
> 
> where  can be SELECT, INSERT, UPDATE or DELETE, the USING
> clause lists the tables of which should be available to the predicate
> and the WHERE clause specifies the predicate.

Certainly an interesting alternative, though you run the very serious
risk that the SQL standard will come up with an extention to the GRANT
syntax that will end up causing problems long-term (this is part of the
reason for the CREATE POLICY syntax that we're currently working

[HACKERS] A mechanism securing web applications in DBMS

[Zhaomo Yang]

Hi all,

I am a graduate student from UC San Diego. My adviser, Dr. Kirill
Levchenko, and I have been working on a web/DB security project for
the last few months. Since fine-grained access control in DBMS is part
of our project and the PostgreSQL community is also working on it now,
we would like to exchange some ideas on the issue with you.

1. Background
Nowadays people entrust more and more sensitive data to web
applications, while security vulnerabilities are common in these
applications. The normal structure of web applications consists of
three tiers: client-side code, server-side code and a database. In
server-side code a database user representing all the
application-level users interacts with the database with full
privileges. Since currently database built-in access control is too
coarse, no access control mechanism is used in most of web
applications. This situation is not ideal since a malicious
application user can tamper with other users’ data by exploiting
vulnerabilities in the application, and if the application is
completely under the malicious user’s control so is the database.

2. Our Mechanism.
In order to fix the problem, we believe there are two underlying
problems to be addressed: authenticating application-level users to
DBMS and fine-grained access control in the DBMS. Note that these two
problems are related. Without authenticating application-level users
to the DBMS, can the DBMS not know which application-level user it is
actually interacting with, thus the DBMS cannot apply fine-grained
access control policy correctly.

2.1 The authentication problem
We address the authentication problem by requiring developers to
define an authentication function in the DBMS. This function is
invoked whenever an application-level user logs in. An authentication
function contains the authentication logic in the server-side code.
Besides, it also caches some attributes of the current
application-level user, which will be checked by the fine-grained
access control mechanism.

In our implementation, we added a new type of function called
authentication function. Developers are supposed to use CREATE
AUTHENTICATION FUNCTION command to define authentication function,
which contains the application authentication logic and must be
defined to return table rows. When the authentication function is
invoked, the return values are cached in a temp table, called the
authentication table,  with the same name as the authentication
function. The authentication table is available to access control
statements (GRANT-WHERE statements).

2.2 The fined-grained access control problem

We address the fined-grained access control problem by using
GRANT-WHERE statements from [1]. Concretely, the syntax of GRANT-WHERE
statements is

  GRANT  [, ...] ON  TO  [, ...]
  USING  [, ...]
  WHERE ;

where  can be SELECT, INSERT, UPDATE or DELETE, the USING
clause lists the tables of which should be available to the predicate
and the WHERE clause specifies the predicate.

3. Integrate our mechanism to PostgreSQL.

3.1 Authentication Function

The major challenge of this part is how to cache users’ authentication
information. In our prototype implementation, an authentication table
is actually a temporary table. But it makes our GRANT-WHERE statements
also temporary since they refer to the authentication table. Since
every session has its own authentication table and all the
authentication tables share the same schema, the global temporary
table [2] would be a perfect fit for caching  users’ authentication
information. Also, it will not make GRANT-WHERE statements temporary
anymore.

3.2 Fined-Grained Access Control

We noticed that the PostgreSQL community is currently working on
fine-grained access control. Access control policies can be defined in
form of “ALTER TABLE … SET ROW SECURITY” statement. Although
functionally “ALTER TABLE … SET ROW SECURITY” statements and
GRANT-WHERE statements are equivalent, however, there are some
advantages in the latter one:

1) GRANT-WHERE statement is very similar to the SQL GRANT statement,
which should be already familiar to most of the developers, thus the
developers may feel more comfortable to use it.

2) In GRANT-WHERE syntax, a DB user can be specified as the recipient
of the GRANT. In the current proposed row security syntax, one needs
to write a big statement containing all the predicates for all the
possible recipients per table per operation.

For example, suppose we have the following two GRANT-WHERE statements on table1

GRANT UPDATE ON table1 TO Alice
WHERE p1;

GRANT UPDATE ON table1 TO Bob
WHERE p2;

The equivalent statement in the “ALTER TABLE … SET ROW SECURITY” syntax is

ALTER TABLE table1 SET ROW SECURITY FOR UPDATE

TO WHERE (current_user = ‘Alice’ AND p1)

OR (current_user = ‘Bob’ AND p2);.

As you can see, this syntax requires us to combine two logically
separate statement into one statement. Also, it can be hard for
developers to debug.

4. Co

Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 5:07 PM, Peter Geoghegan  wrote:
> So I came up with what I imagined to be an unsympathetic case:

BTW, this "cities" data is still available from:

http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/data/cities.dump

-- 
Peter Geoghegan


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

Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 11:12 AM, Peter Geoghegan  wrote:
> What I
> consider an open question is whether or not we should do that on the
> first call when there is no abbreviated comparison, such as on the
> second or subsequent attribute in a multi-column sort, in the hope
> that equality will just happen to be indicated.

> Let me try and come up with some numbers for a really unsympathetic
> case, since you've already seen sympathetic numbers.

So I came up with what I imagined to be an unsympathetic case:

postgres=# create table opt_eq_test as select 1 as dummy, country ||
', ' || city as country  from cities order by city;
SELECT 317102
[local]/postgres=# select * from opt_eq_test limit 5;
 dummy | country
---+--
 1 | India, 108 Kalthur
 1 | Mexico, 10 de Abril
 1 | India, 113 Thalluru
 1 | Argentina, 11 de Octubre
 1 | India, 11 Dlp
(5 rows)

I added the dummy column to prevent abbreviated keys from being used -
this is all about the question of trying to get away with a "memcmp()
== 0" in all circumstances, without abbreviated keys/statistics on
attribute cardinality. This is a question that has nothing to do with
abbreviated keys in particular.

With the most recent revision of the patch, performance of a
representative query against this data stabilizes as follows on my
laptop:

LOG:  duration: 2252.500 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2261.505 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2315.903 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2260.132 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2247.340 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2246.723 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2276.297 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2241.911 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2259.540 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2248.740 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2245.913 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2230.583 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;

If I apply the additional optimization that we're on the fence about:

--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -1967,7 +1967,7 @@ bttextfastcmp_locale(Datum x, Datum y, SortSupport ssup)
len1 = VARSIZE_ANY_EXHDR(arg1);
len2 = VARSIZE_ANY_EXHDR(arg2);

-   if (ssup->abbrev_state == ABBREVIATED_KEYS_TIE && len1 == len2)
+   if (len1 == len2)
{
/*
 * Being called as authoritative tie-breaker for an
abbreviated key

Then the equivalent numbers look like this:

LOG:  duration: 2178.220 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2175.005 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2219.648 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2174.865 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2246.387 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2234.023 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2186.957 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2177.778 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2186.709 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2171.557 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2211.822 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2224.198 ms  statement: select * from (select * from
opt_eq_test order by dummy, country offset 100) d;
LOG:  duration: 2192.506 ms  statement: select * from (select * from
opt_eq_test order by du

[HACKERS] settings without unit

[Euler Taveira]

Hi,

I noticed that a setting in pg_settings without units have NULL and ""
as unit values ("" for integer and NULL for the other ones). Could we be
consistent? It is like that since units were introduced (b517e65). No
unit means unit = NULL. A proposed patch is attached.


-- 
   Euler Taveira   Timbira - http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
*** guc.c.orig	2014-09-04 20:16:09.108040521 -0300
--- guc.c	2014-09-04 17:48:35.113897896 -0300
***
*** 7690,7696 
  values[2] = "min";
  break;
  			default:
! values[2] = "";
  break;
  		}
  	}
--- 7690,7696 
  values[2] = "min";
  break;
  			default:
! values[2] = NULL;
  break;
  		}
  	}

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

[Florian Pflug]

On Sep4, 2014, at 20:50 , Pavel Stehule  wrote:
> 2014-09-04 20:31 GMT+02:00 Josh Berkus :
> * The ability to "compile" functions/procedures for faster execution.
> 
> This point is more complex, because bottleneck is not in plpgsql - it is
> terrible fast against noncompiled pcode interpreted PL/SQL and it is
> comparable with PL/SQL - due different design. A expression evaluation is
> slower, partially due using a SQL expression interpret, partially due our
> arrays and strings are immutable, and any composition are slow.

That, in principle, is just an inlining problem, though. Say we translate
PL/pgSQL into LLVM bytecode in the simplest possible way by simply traversing
the parse tree, and emitting calls to the functions that the interpreter calls
now. Now, that alone wouldn't buy much, as you say. But if we additionally
compile (at least parts of) the executor machinery to LLVM bytecode too
(just once, while building postgres), the LLVM optimizer should in principle
be able to inline at least some of these calls, which *could* have considerable
benefit. The hard part would probably be to figure out how to inform the
executor which parts it may consider to be *constant* (i.e. what constitues
the execution *plan*) and which parts can change from one execution to the
next (i.e. the executor state). 

In fact, such an approach would allow all expression evaluations to be
JITed - not only those appearing in PL/pgSQL functions but also in plain SQL.

> Cost of hidden IO cast is negative too. If we can change it, then we can
> increase a sped.

But the whole power of PL/pgSQL comes from the fact that it allows you to
use the full set of postgres data types and operatores, and that it seamlessly
integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC
as a programming language...

best regards,
Florian Pflug



-- 
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] PQputCopyEnd doesn't adhere to its API contract

[David Johnston]

On Thu, Sep 4, 2014 at 5:13 PM, Robert Haas  wrote:

> On Thu, Sep 4, 2014 at 1:18 PM, David G Johnston
>  wrote:
> > Specific observations would help though that is partly the idea - I've
> been
> > more focused on clarity and organization even if it requires deviating
> from
> > the current general documentation style.
>
> OK.
>
> -   to the network connection used by libpq.
> +   to the connection used by libpq.
>
> This change is unrelated to the topic at hand and seems not to be an
> improvement.
>

​Fair point - though I did question the necessity of "network" in the
accompanying e-mail.

​The implied suggestion is that if I do find any other areas that look like
they need fixing - even in the same file - I should separate them out into
a separate patch.  Though I have seen various "while I was in there I also
fixed such-and-such" commits previously so the line is at least somewhat
fluid.​


> +  
> +   
> +Please review the function notes for specific interface protocols -
> +the following is a simplified overview.
> +   
> +  
>
> This seems pointless.  Of course general documentation will be less
> specific than documentation for specific functions.
>

​The existing wording was being verbose in order to be correct.  In a
summary like this I'd trade being reasonably accurate and general for the
precision that is included elsewhere.


>
> +   First, the application issues the SQL
> COPY command via PQexec or one
> +   of the equivalent functions.  The response
> +   will either be an error or a PGresult object bearing
> +   a status code for PGRES_COPY_OUT or
> +   PGRES_COPY_IN call implied by the specified copy
> +   direction (TO or FROM respectively).
>
> This implies that the response won't be a PGresult in the error case,
> but of course the function has the same C result type either way.
>
>
​One of the trade-offs I mentioned...its more style than anything but
removing the parenthetical (if there is not error in the command) and
writing it more directly seemed preferable in an overview such as this.

Better:  The function will either throw an error or return a PGresult
object[...]​

+  
> +   Second, the application should use the functions in this
> +   section to receive data rows or transmit buffer loads.  Buffer loads
> are
> +   not guaranteed to be processed until the copy transfer is completed.
> +  
>
> The main change here vs. the existing text is that you're now using
> the phase "buffer loads" to refer to what gets transmitted, and "data
> rows" to refer to what gets received.  The existing text uses the term
> "data rows" for both, which seems correct to me.  My first reaction on
> reading your revised text was "wait, what's a buffer load?".
>

​So, my generalization policy working in reverse - since the transmit side
does not have to be in complete rows implying that they are here is (albeit
acceptably) inaccurate.​



>
> +   Third, as lastly, when the data transfer is
> +   complete the client must issue a PQgetResult to "commit" the copy
> transfer
> +   and get the final PGresult object that indicates
>
> I assume you mean "and lastly", since "as lastly" doesn't sound like
> good English.
>

​Yep.



>
> -   At this point further SQL commands can be issued via
> -   PQexec.  (It is not possible to execute other SQL
> -   commands using the same connection while the COPY
> -   operation is in progress.)
>
> Removing this text doesn't seem like a good idea.  It's a quite
> helpful clarification.  The  you've added in its place doesn't
> seem like a good substitute for it, and more generally, I think we
> should avoid the overuse of constructs like .  Emphasis needs to
> be used minimally or it loses its meaning.
>

​Was trying to remove repetition here - happy to consider alternative way
of doing so if the note is objectionable.​



> > If this is not acceptable I'm happy to incorporate the ideas of others to
> > try and get the best of both worlds.
>
> +   
> +The return value of both these function can be one of [-1, 0, 1]
> +whose meaning depends on whether you are in blocking or non-blocking
> mode.
> +   
>
> The use of braces to list a set of possible values is not standard in
> mathematics generally, our documentation, or any other documentation I
> have seen.
>

Agreed



>
> +   
> +Non-Blocking Mode: A value of 1 means that the payload was
> +placed in the queue while a -1 means an immediate and permanent
> failure.
> +A return value of 0 means that the queue was full: you need to try
> again
> +at the next wait-ready.
> +   
>
> We generally avoid emphasizing captions or headings in this way.  The
> markup engine has no knowledge that "Non-Blocking Mode" is special; it
> will format and style this just as if you had written "This is how it
> works: a value of 1 means...".  That's probably not appropriate. Our
> style is to write English prose using full sentences, e.g. "In
> non-blocking mode, a value of 1 means...".
>

​Was a litt

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Bruce Momjian]

On Thu, Sep  4, 2014 at 03:24:05PM -0600, Noah Yetter wrote:
> Doing the upgrade with an installation built from REL9_3_STABLE at
> commit 52eed3d4267faf671dae0450d99982cb9ba1ac52 was successful.
> 
> The view that I saw get re-created as a table doesn't have any circular
> references, or indeed any references to other views, nor do any other views
> reference it.  But since it does seem that there are valid cases where a view
> gets temporarily re-created as a table during an upgrade, I'm going to assume
> it's not a bug per se.  My upgraded cluster using built-from-source binaries
> has these views as views, so when the process is complete they end up in the
> correct state.
> 
> Is there an expected release date for 9.3.6?

I don't know, but your report makes it clear we will need one sooner
rather than later.

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

  + Everyone has their own god. +


-- 
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] B-Tree support function number 3 (strxfrm() optimization)

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 2:18 PM, Robert Haas  wrote:
> Eh, maybe?  I'm not sure why the case where we're using abbreviated
> keys should be different than the case we're not.  In either case this
> is a straightforward trade-off: if we do a memcmp() before strcoll(),
> we win if it returns 0 and lose if returns non-zero and strcoll also
> returns non-zero.  (If memcmp() returns non-zero but strcoll() returns
> 0, it's a tie.)  I'm not immediately sure why it should affect the
> calculus one way or the other whether abbreviated keys are in use; the
> question of how much faster memcmp() is than strcoll() seems like the
> relevant consideration either way.


Not quite. Consider my earlier example of sorting ~300,000 cities by
country only. That's a pretty low cardinality attribute. We win big,
and we are almost certain that the abbreviated key cardinality is a
perfect proxy for the full key cardinality so we stick with
abbreviated keys while copying over tuples. Sure, most comparisons
will actually be resolved with a "memcmp() == 0" rather than an
abbreviated comparison, but under my ad-hoc cost model there is no
distinction, since they're both very much cheaper than a strcoll()
(particularly when we factor in the NUL termination copying that a
"memcmp() == 0" also avoids). To a lesser extent we're also justified
in that optimism because we've already established that roughly the
first 8 bytes of the string are bitwise equal.

So the difference is that in the abbreviated key case, we are at least
somewhat justified in our optimism. Whereas, where we're just eliding
fmgr overhead, say on the 2nd or subsequent attribute of a multi-key
sort, it's totally opportunistic to chance a "memcmp() == 0". The
latter optimization can only be justified by the fact that the
memcmp() is somewhere between dirt cheap and free. That seems like
soemthing that should significantly impact the calculus.

-- 
Peter Geoghegan


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

Re: [HACKERS] PL/pgSQL 2

[Robert Haas]

On Thu, Sep 4, 2014 at 2:31 PM, Josh Berkus  wrote:
> Sadly, what's prevented us from having "packages" already has been the
> insistence of potential feature sponsors that they work *exactly* like
> PL/SQL's packages, which is incompatible with Postgres namespacing.
> Also, we'd want any "package" concept to be usable with external PLs as
> well as PL/pgSQL, which necessitates other Oracle-incompatible changes.

This is not a fun area in which to try to be exactly like Oracle.
Just to take one example, the whole package is created and dumped as a
single object, with all of its contained functions *and their
comments*, including the exact position of those comments, such as
inside the argument list to document what particular arguments are
supposed to do.  We've worked out a (partial) solution to that problem
in Advanced Server, but it's not perfect, and it limits the ability to
implement other features that PostgreSQL users would probably expect,
like being able to add a function to a package after-the-fact.
PostgreSQL has a certain cleanliness of design that comes from doing
things in a way that makes sense from first principles, rather than
the way that other people may have done it.  I'm not prepared to say
that a $184B company made a bad design decision here - it certainly
seems to have worked out for them - but it's not what I would have
picked, and it's not a very good fit for other design decisions we've
made in PostgreSQL already.

All-in-all, I'm pretty happy with our EXTENSION system as a way of
loading code (and SQL function definitions) in a modular way.  It's
not perfect, but it's definitely made my life as a developer easier.
There are some things you can do with an Oracle package but not a
PostgreSQL extension, but there is an awful lot of overlap, too.  I
doubt we'd want to duplicate all that machinery just for compatibility
reasons.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Noah Yetter]

Doing the upgrade with an installation built from REL9_3_STABLE at
commit 52eed3d4267faf671dae0450d99982cb9ba1ac52 was successful.

The view that I saw get re-created as a table doesn't have any circular
references, or indeed any references to other views, nor do any other views
reference it.  But since it does seem that there are valid cases where a
view gets temporarily re-created as a table during an upgrade, I'm going to
assume it's not a bug per se.  My upgraded cluster using built-from-source
binaries has these views as views, so when the process is complete they end
up in the correct state.

Is there an expected release date for 9.3.6?


On Thu, Sep 4, 2014 at 2:01 PM, Bruce Momjian  wrote:

> On Thu, Sep  4, 2014 at 03:48:17PM -0400, Robert Haas wrote:
> > On Thu, Sep 4, 2014 at 3:35 PM, Bruce Momjian  wrote:
> > >> At any rate, I've additionally observed that the relation which is
> blowing up
> > >> pg_upgrade is a VIEW in the source cluster but gets created as a
> TABLE in the
> > >> upgraded cluster, which may better explain why it had no toast table
> before and
> > >> now it does.  Is this some kind of expected behavior for views?
> > >
> > > Uh, it certainly should not be creating a table instead of a view,
> > > though it will get a pg_class entry.
> >
> > Actually, there's a way this can happen.  If you create two (or more)
> > views with circular dependencies between them, then pg_dump will emit
> > commands to create one of them as a table first, then create the
> > others as views, then convert the first table to a view by adding a
> > _SELECT rule to it.
>
> Wow, that's super-interesting.
>
> > If pg_upgrade's logic can't cope with that, that's a bug in
> > pg_upgrade, because there's no other way to restore views with
> > circular dependency chains.
>
> I don't see why pg_upgrade would have any problem with it as it just
> looks at the old schema and post-restore schema.
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + Everyone has their own god. +
>

Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)

[Robert Haas]

On Thu, Sep 4, 2014 at 2:12 PM, Peter Geoghegan  wrote:
> On Thu, Sep 4, 2014 at 9:19 AM, Robert Haas  wrote:
>> On Tue, Sep 2, 2014 at 10:27 PM, Peter Geoghegan  wrote:
>>> * Still doesn't address the open question of whether or not we should
>>> optimistically always try "memcmp() == 0" on tiebreak. I still lean
>>> towards "yes".
>>
>> Let m be the cost of a memcmp() that fails near the end of the
>> strings; and let s be the cost of a strcoll that does likewise.
>> Clearly s > m.  But approximately what is s/m on platforms where you
>> can test?  Say, with 100 byte string, in a few different locales.
>
> Just to be clear: I imagine you're more or less sold on the idea of
> testing equality in the event of a tie-break, where the leading 8
> primary weight bytes are already known to be equal (and the full text
> string lengths also match); the theory of operation behind testing how
> good a proxy for full key cardinality abbreviated key cardinality is
> is very much predicated on that. We can still win big with very low
> cardinality sets this way, which are an important case. What I
> consider an open question is whether or not we should do that on the
> first call when there is no abbreviated comparison, such as on the
> second or subsequent attribute in a multi-column sort, in the hope
> that equality will just happen to be indicated.

Eh, maybe?  I'm not sure why the case where we're using abbreviated
keys should be different than the case we're not.  In either case this
is a straightforward trade-off: if we do a memcmp() before strcoll(),
we win if it returns 0 and lose if returns non-zero and strcoll also
returns non-zero.  (If memcmp() returns non-zero but strcoll() returns
0, it's a tie.)  I'm not immediately sure why it should affect the
calculus one way or the other whether abbreviated keys are in use; the
question of how much faster memcmp() is than strcoll() seems like the
relevant consideration either way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Commitfest status

[Stephen Frost]

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:
> Stephen Frost wrote:
> > * Heikki Linnakangas (hlinnakan...@vmware.com) wrote:
> > > 5. Better syntax for REINDEX
> 
> > > I think the latter 3 patches are missing a reviewer because no-one
> > > is interested in them. There was some discussion on the REINDEX
> > > syntax, and whether we want the patch at all. The pgcrypto patches
> > > have received zero comments.
> > 
> > I'm certainly interested in the pgcrypto patches and can look at REINDEX
> > this weekend.
> 
> I can take care of the reindex one --- I'm already on it anyway, waiting
> for Vik to post the updated version per the respective thread.

Works for me.  I've marked you as reviewer.

I'll check out some of the 'ready for committer' ones.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] PQputCopyEnd doesn't adhere to its API contract

[Robert Haas]

On Thu, Sep 4, 2014 at 1:18 PM, David G Johnston
 wrote:
> Specific observations would help though that is partly the idea - I've been
> more focused on clarity and organization even if it requires deviating from
> the current general documentation style.

OK.

-   to the network connection used by libpq.
+   to the connection used by libpq.

This change is unrelated to the topic at hand and seems not to be an
improvement.

+  
+   
+Please review the function notes for specific interface protocols -
+the following is a simplified overview.
+   
+  

This seems pointless.  Of course general documentation will be less
specific than documentation for specific functions.

+   First, the application issues the SQL
COPY command via PQexec or one
+   of the equivalent functions.  The response
+   will either be an error or a PGresult object bearing
+   a status code for PGRES_COPY_OUT or
+   PGRES_COPY_IN call implied by the specified copy
+   direction (TO or FROM respectively).

This implies that the response won't be a PGresult in the error case,
but of course the function has the same C result type either way.

+  
+   Second, the application should use the functions in this
+   section to receive data rows or transmit buffer loads.  Buffer loads are
+   not guaranteed to be processed until the copy transfer is completed.
+  

The main change here vs. the existing text is that you're now using
the phase "buffer loads" to refer to what gets transmitted, and "data
rows" to refer to what gets received.  The existing text uses the term
"data rows" for both, which seems correct to me.  My first reaction on
reading your revised text was "wait, what's a buffer load?".

+   Third, as lastly, when the data transfer is
+   complete the client must issue a PQgetResult to "commit" the copy transfer
+   and get the final PGresult object that indicates

I assume you mean "and lastly", since "as lastly" doesn't sound like
good English.

-   At this point further SQL commands can be issued via
-   PQexec.  (It is not possible to execute other SQL
-   commands using the same connection while the COPY
-   operation is in progress.)

Removing this text doesn't seem like a good idea.  It's a quite
helpful clarification.  The  you've added in its place doesn't
seem like a good substitute for it, and more generally, I think we
should avoid the overuse of constructs like .  Emphasis needs to
be used minimally or it loses its meaning.

> If this is not acceptable I'm happy to incorporate the ideas of others to
> try and get the best of both worlds.

+   
+The return value of both these function can be one of [-1, 0, 1]
+whose meaning depends on whether you are in blocking or non-blocking mode.
+   

The use of braces to list a set of possible values is not standard in
mathematics generally, our documentation, or any other documentation I
have seen.

+   
+Non-Blocking Mode: A value of 1 means that the payload was
+placed in the queue while a -1 means an immediate and permanent failure.
+A return value of 0 means that the queue was full: you need to try again
+at the next wait-ready.
+   

We generally avoid emphasizing captions or headings in this way.  The
markup engine has no knowledge that "Non-Blocking Mode" is special; it
will format and style this just as if you had written "This is how it
works: a value of 1 means...".  That's probably not appropriate. Our
style is to write English prose using full sentences, e.g. "In
non-blocking mode, a value of 1 means...".

-   into buffer loads of any convenient size.  Buffer-load boundaries
+   into buffer-loads of any convenient size.  Buffer-load boundaries

Well, OK, here's a mention of buffer loads in the existing
documentation.  But, when you previously used the term, you didn't
hyphenate it, but here you are changing it to be hyphenated.  Note
that the fact that it's hyphenated the second time on this line is
because the two-word term is being used as an adjective modifying
boundaries, not because every use of those two words should be
hyphenated.  (Consider how odd the previous sentence would look if I
had written it this way: ...not because every use of those two-words
should be hyphenated.  Yet the earlier hyphenation of two-word looks
correct, at least to me, for the same reasons as in the
documentation.)

-   Ends the COPY_IN operation successfully if
-   errormsg is NULL.  If
-   errormsg is not NULL then the
-   COPY is forced to fail, with the string pointed to by
-   errormsg used as the error message.  (One should not
-   assume that this exact error message will come back from the server,
-   however, as the server might have already failed the
-   COPY for its own reasons.  Also note that the option
-   to force failure does not work when using pre-3.0-protocol
-   connections.)
+   If errormsg is NULL this ends
+   the COPY_IN operation successfully; otherwise the
+   COPY

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

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 11:55 AM, Peter Geoghegan  wrote:
> It's not an immediate concern, though.

My immediate concern is to get some level of buy-in about how
everything fits together at a high level. Separately, as discussed in
my opening mail, there is the question of how value locking should
ultimately be implemented. These are two orthogonal questions, or are
pretty close to orthogonal. That helps. It also helps that people have
stopped being confused by the term "value locking" (I think).

I'm tempted to believe that the silence on the question of how things
fit together (such as the lack of discussion of my pgCon talk's
characterization of a "pick any 2" trade-off) means that that's
because everyone agrees with that. That seems pretty naive, though,
because a lot of the issues are very subtle. I think that various
interested people, including Robert and Andres have yet to make their
minds up on that. I'm not sure what Tom thinks of it.

-- 
Peter Geoghegan


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

Re: [HACKERS] Commitfest status

[Alvaro Herrera]

Stephen Frost wrote:
> * Heikki Linnakangas (hlinnakan...@vmware.com) wrote:
> > 5. Better syntax for REINDEX

> > I think the latter 3 patches are missing a reviewer because no-one
> > is interested in them. There was some discussion on the REINDEX
> > syntax, and whether we want the patch at all. The pgcrypto patches
> > have received zero comments.
> 
> I'm certainly interested in the pgcrypto patches and can look at REINDEX
> this weekend.

I can take care of the reindex one --- I'm already on it anyway, waiting
for Vik to post the updated version per the respective thread.

-- 
Álvaro Herrerahttp://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] Join push-down support for foreign tables

[Robert Haas]

On Thu, Sep 4, 2014 at 11:56 AM, Bruce Momjian  wrote:
>> I am thinking eventually we will need to cache the foreign server
>> statistics on the local server.
>>
>> Wouldn't that lead to issues where the statistics get outdated and we have to
>> anyways query the foreign server before planning any joins? Or are you 
>> thinking
>> of dropping the foreign table statistics once the foreign join is complete?
>
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server.  I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.

Maybe ... but I think this isn't really related to the ostensible
topic of this thread.  We can do join pushdown just fine without the
ability to do anything like this.

I'm in full agreement that we should probably have a way to cache some
kind of statistics locally, but making that work figures to be tricky,
because (as I'm pretty sure Tom has pointed out before) there's no
guarantee that the remote side's statistics look anything like
PostgreSQL statistics, and so we might not be able to easily store
them or make sense of them.  But it would be nice to at least have the
option to store such statistics if they do happen to be something we
can store and interpret.

It's also coming to seem to me more and more that we need a way to
designate several PostgreSQL machines as a cooperating cluster.  This
would mean they'd keep connections to each other open and notify each
other about significant events, which could include "hey, I updated
the statistics on this table, you might want to get the new ones" or
"hey, i've replicated your definition for function X so it's safe to
push it down now" as well as "hey, I have just been promoted to be the
new master" or even automatic negotiation of which of a group of
machines should become the master after a server failure.  So far,
we've taken the approach that postgres_fdw is just another FDW which
enjoys no special privileges, and I think that's a good approach on
the whole, but  think if we want to create a relatively seamless
multi-node experience as some of the NoSQL databases do, we're going
to need something more than that.

But all of that is a bit pie in the sky, and the join pushdown
improvements we're talking about here don't necessitate any of it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] vacuumdb --all --analyze-in-stages - wrong order?

[Pavel Stehule]

2014-09-04 5:36 GMT+02:00 Peter Eisentraut :

> On Mon, 2014-05-19 at 13:51 -0400, Peter Eisentraut wrote:
> > On 5/18/14, 3:52 AM, Pavel Stehule wrote:
> > > I am looking on --analyze-in-stages option. If I understand well,
> > > motivation for this option is a get some minimal statistic for
> databases
> > > in minimal time. But when I tested, I found so iterations are per
> > > databases, not per stages - some first database get a maximum
> statistics
> > > and second has zero statistics. Isn't it unpractical?
> >
> > Yes.  Let me see if I can fix that.
>
> At long last, here is a patch.
>
> If somebody has an idea how to code some of that less confusingly, let
> me know.
>
> It is little bit hard to read.

/* If stage is -1, then run all stages.  Otherwise, we got
a stage
 * from vacuum_all_databases(), so just run that one. */
for (i = (stage == -1 ? 0 : stage); i < (stage == -1 ? 3 :
stage + 1); i++)
{
puts(gettext(stage_messages[i]));
executeCommand(conn, stage_commands[i], progname,
echo);
run_vacuum_command(conn, sql.data, echo, dbname,
table, progname);
}

maybe better be more verbose - and it can be in alone function, because it
is "analyze only"

if (stage == -1)
{
  for (i = 0; i < 3; i++)
  {
puts(gettext(stage_messages[i]));
executeCommand(conn, stage_commands[i], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
  }
}
else
{
puts(gettext(stage_messages[stage]));
executeCommand(conn, stage_commands[stage], progname, echo);
run_vacuum_command(conn, sql.data, echo, dbname, table, progname);
}

Regards

Pavel

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Bruce Momjian]

On Thu, Sep  4, 2014 at 03:48:17PM -0400, Robert Haas wrote:
> On Thu, Sep 4, 2014 at 3:35 PM, Bruce Momjian  wrote:
> >> At any rate, I've additionally observed that the relation which is blowing 
> >> up
> >> pg_upgrade is a VIEW in the source cluster but gets created as a TABLE in 
> >> the
> >> upgraded cluster, which may better explain why it had no toast table 
> >> before and
> >> now it does.  Is this some kind of expected behavior for views?
> >
> > Uh, it certainly should not be creating a table instead of a view,
> > though it will get a pg_class entry.
> 
> Actually, there's a way this can happen.  If you create two (or more)
> views with circular dependencies between them, then pg_dump will emit
> commands to create one of them as a table first, then create the
> others as views, then convert the first table to a view by adding a
> _SELECT rule to it.

Wow, that's super-interesting.

> If pg_upgrade's logic can't cope with that, that's a bug in
> pg_upgrade, because there's no other way to restore views with
> circular dependency chains.

I don't see why pg_upgrade would have any problem with it as it just
looks at the old schema and post-restore schema.

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

  + Everyone has their own god. +


-- 
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] Commitfest status

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 12:42 PM, Stephen Frost  wrote:
> I'm certainly interested in the pgcrypto patches and can look at REINDEX
> this weekend.

I'm thinking of picking one of these up, but I'll be on vacation next
week, and so probably won't get to it until the 15th at the earliest.
The hash join patch looks interesting.

-- 
Peter Geoghegan


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

Re: [HACKERS] PL/pgSQL 2

[Stephen Frost]

* Robert Haas (robertmh...@gmail.com) wrote:
> Second, if you did manage to develop something which was significantly
> more compatible with Oracle than PostgreSQL or PL/pgsql is today,
> you'd probably find that the community wouldn't accept it.

Agreed.  Moving PostgreSQL forward is what the community is interested
in- not duplicating what another database product has for the strict
goal of easing migrations from those databases (be it Oracle or MSSQL or
MySQL).

> To take another example, I've been complaining about the fact
> that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> than any other database I'm aware of for years, probably since before
> I joined EnterpriseDB.  And I still think we're kidding ourselves to
> think that we've got that right when nobody else is doing something
> similar.  I don't think the community should reverse that decision to
> benefit EnterpriseDB, or to be compatible with Oracle: I think the
> community should reverse that decision because it's stupid, and the
> precedent of other systems demonstrates that it is possible to do
> better.  Oracle's handling of reserved words also seems to be
> considerably less irritating than ours, and I'd propose that we
> improve that in PostgreSQL too, if I knew how to do it.
> Unfortunately, I suspect that requires jettisoning bison and rolling
> our own parser generator, and it's hard to argue that would be a good
> investment of effort for the benefit we'd get.

Also agreed on this, though any serious discussion on this would deserve
its own thread.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Robert Haas]

On Thu, Sep 4, 2014 at 3:35 PM, Bruce Momjian  wrote:
>> At any rate, I've additionally observed that the relation which is blowing up
>> pg_upgrade is a VIEW in the source cluster but gets created as a TABLE in the
>> upgraded cluster, which may better explain why it had no toast table before 
>> and
>> now it does.  Is this some kind of expected behavior for views?
>
> Uh, it certainly should not be creating a table instead of a view,
> though it will get a pg_class entry.

Actually, there's a way this can happen.  If you create two (or more)
views with circular dependencies between them, then pg_dump will emit
commands to create one of them as a table first, then create the
others as views, then convert the first table to a view by adding a
_SELECT rule to it.

If pg_upgrade's logic can't cope with that, that's a bug in
pg_upgrade, because there's no other way to restore views with
circular dependency chains.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Commitfest status

[Stephen Frost]

* Heikki Linnakangas (hlinnakan...@vmware.com) wrote:
> 5. Better syntax for REINDEX
> 6. pgcrypto: support PGP signatures
> 7. pgcrypto: PGP armour headers

[...]

> I think the latter 3 patches are missing a reviewer because no-one
> is interested in them. There was some discussion on the REINDEX
> syntax, and whether we want the patch at all. The pgcrypto patches
> have received zero comments.

I'm certainly interested in the pgcrypto patches and can look at REINDEX
this weekend.

> If you think that a feature is worthwhile, please sign up as a
> reviewer. If these patches don't have a reviewer assigned by the end
> of the week, I'm going to mark them as Rejected on the grounds that
> no-one cares about them.

Looks like Joel has picked up the pgcrypto ones (though I'd still be
interested to help as a committer) and I'll get with Vik about the
REINDEX patch.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Bruce Momjian]

On Thu, Sep  4, 2014 at 01:14:01PM -0600, Noah Yetter wrote:
> Isn't that exactly what the release note says? 
> "where the new server creates a TOAST table but the old version did not" 
> vs. 
> "where the new cluster needs a TOAST table that the old cluster didn't"

Sorry, yes, I got confused.  We have always handled cases where the old
cluster needed a TOAST table and the new cluster didn't.  The 9.3.5 fix
is to prevent a certain failure for a new-only TOAST table:

commit 3088cc37044a303fc50857d8d9e7e44b5c250642
Author: Bruce Momjian 
Date:   Mon Jul 7 13:24:08 2014 -0400

pg_upgrade: allow upgrades for new-only TOAST tables

Previously, when calculations on the need for toast tables changed,
pg_upgrade could not handle cases where the new cluster needed a 
TOAST
table and the old cluster did not.  (It already handled the opposite
case.)  This fixes the "OID mismatch" error typically generated in 
this
case.

Backpatch through 9.2

The post-9.3.5 fix is for OID conflict that _can_ happen from a new-only
TOAST tables:

commit 4c6780fd17aa43ed6362aa682499cc2f9712cc8b
Author: Bruce Momjian 
Date:   Thu Aug 7 14:56:13 2014 -0400

pg_upgrade: prevent oid conflicts with new-cluster TOAST tables

Previously, TOAST tables only required in the new cluster could 
cause
oid conflicts if they were auto-numbered and a later conflicting 
oid had
to be assigned.

Backpatch through 9.3


> At any rate, I've additionally observed that the relation which is blowing up
> pg_upgrade is a VIEW in the source cluster but gets created as a TABLE in the
> upgraded cluster, which may better explain why it had no toast table before 
> and
> now it does.  Is this some kind of expected behavior for views?

Uh, it certainly should not be creating a table instead of a view,
though it will get a pg_class entry.

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

  + Everyone has their own god. +


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

[Pavel Stehule]

Hi

I did a review of last patch

1. There is no problem with patching
2. compilation and doc compilation without warnings and issues.
3. code is clean, respects Postgres coding rules and is well documented -
it is slightly modified Tom's version with float8 optimization
4. The name with_bucket is probably one with wide agreement
5. There are a basic set of tests for muttable or fixed sized types

I found only one issue - float8 path has no own test in regress tests. When
this issue will be fixed, I will mark this patch as ready for commit

Regards

Pavel



2014-09-01 21:29 GMT+02:00 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
>
>

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Noah Yetter]

Isn't that exactly what the release note says?
"where the new server creates a TOAST table but the old version did not"
vs.
"where the new cluster needs a TOAST table that the old cluster didn't"

At any rate, I've additionally observed that the relation which is blowing
up pg_upgrade is a VIEW in the source cluster but gets created as a TABLE
in the upgraded cluster, which may better explain why it had no toast table
before and now it does.  Is this some kind of expected behavior for views?


On Thu, Sep 4, 2014 at 12:39 PM, Bruce Momjian  wrote:

> On Thu, Sep  4, 2014 at 11:37:27AM -0600, Noah Yetter wrote:
> > The 9.3.5 release notes contain...
> >
> >
> >   • Fix pg_upgrade for cases where the new server creates a TOAST table
> but the
> > old version did not (Bruce Momjian)
> >
> > This rare situation would manifest as "relation OID mismatch" errors.
> >
> >
> > ...which I thought was this bug, hence my confusion.  If anyone else is
> > experiencing this bug, they may erroneously be led to believe that 9.3.5
> > contains the fix.
> >
> >
> > I will attempt to build 9.3 stable head and retry my upgrade.
>
> Yes, please let us know.  The post-9.3.5 fix is for the reverse case,
> where the new cluster needs a TOAST table that the old cluster didn't.
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + Everyone has their own god. +
>

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[David G Johnston]

On Thu, Sep 4, 2014 at 2:39 PM, Bruce Momjian [via PostgreSQL] <
ml-node+s1045698n5817828...@n5.nabble.com> wrote:

> On Thu, Sep  4, 2014 at 11:37:27AM -0600, Noah Yetter wrote:
>
> > The 9.3.5 release notes contain...
> >
> >
> >   • Fix pg_upgrade for cases where the new server creates a TOAST table
> but the
> > old version did not (Bruce Momjian)
> >
> > This rare situation would manifest as "relation OID
> mismatch" errors.
> >
> >
> > ...which I thought was this bug, hence my confusion.  If anyone else is
> > experiencing this bug, they may erroneously be led to believe that 9.3.5
> > contains the fix.
> >
> >
> > I will attempt to build 9.3 stable head and retry my upgrade.
>
> Yes, please let us know.  The post-9.3.5 fix is for the reverse case,
> where the new cluster needs a TOAST table that the old cluster didn't.
>

​hmmm...the 9.3.5 doc and what you just wrote (and the Aug 7 Patch Commit)
are saying the same thing...both patches claim to fix oid conflicts when
only the new server requires the TOAST table.

I'm not sure, though, whether anything useful can be done except field
questions until 9.3.6 is released.  We cannot fix the 9.3.5 doc at this
point and once 9.3.6 comes out the distinction will be irrelevant...

David J.​




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Pg-upgrade-and-toast-tables-bug-discovered-tp5810447p5817830.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

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

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 8:03 AM, Robert Haas  wrote:
> I think there shouldn't be any plan nodes in the system that don't get
> displayed by explain.  If you're using a plan node for something, and
> think it shouldn't be displayed by explain, then either (1) you are
> wrong or (2) you are abusing the plan node.

Maybe. I admit that I'm not entirely confident that the representation
of the auxiliary state during planning and execution is ideal.
However, it sure is convenient to be able to separately plan the
auxiliary query as a subquery, and not have to specially fish it out
of the subplan list later. Maybe we should add a mechanism that
essentially generates an equivalent, single ModifyTable plan. Or maybe
that would be adding a lot of code for no tangible benefit. I don't
see much point in making one ModifyTable node pull up from the other
for the benefit of this feature (which is another thing entirely to
having there be a single ModifyTable plan). For now, I'm glad to have
something that will allow us to drive discussion of the feature to the
next level. I don't have a good enough understanding of the optimizer
to be able to say with confidence what we should do, or to be able to
see the big picture of making any particular trade-off. It's not an
immediate concern, though.

-- 
Peter Geoghegan


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

Re: [HACKERS] PL/pgSQL 2

[Pavel Stehule]

2014-09-04 20:31 GMT+02:00 Josh Berkus :

> On 09/04/2014 09:02 AM, Craig Ringer wrote:
> > There are a few things I would like to see, like secure session
> > variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
> > compatibility" seems to be something that comes up before the speaker
> > has really understood what that would mean, and the sheer scope of the
> > endeavour.
> >
> > It's not going from 50% compatible to 80% compatible, it's going from 5%
> > compatible to 7% compatible. The most used 5% maybe, but still...
>
> However, there are users who want certain features from PL/SQL not for
> compatibility but because they're useful.  For example:
>
> * A "package" concept for encapsulation of multiple procedures, session
> variables, etc.
>
> * The ability to "compile" functions/procedures for faster execution.
>

This point is more complex, because bottleneck is not in plpgsql - it is
terrible fast against noncompiled pcode interpreted PL/SQL and it is
comparable with PL/SQL - due different design. A expression evaluation is
slower, partially due using a SQL expression interpret, partially due our
arrays and strings are immutable, and any composition are slow. Cost of
hidden IO cast is negative too. If we can change it, then we can increase a
sped. Almost all from these bottlenecks are out of plpgsql engine. So
compilation of plpgsql is myth and it doesn't help and it doesn't need it.
It doesn't help with speed.

Pavel


>
> * Autonomous transactions
>
> We'd also like to borrow stuff from other DBMSes, such as multisets.
> All of the above are worth implementing, even if it means implementing
> them with different syntax (and mechanics) than PL/SQL.
>
> Sadly, what's prevented us from having "packages" already has been the
> insistence of potential feature sponsors that they work *exactly* like
> PL/SQL's packages, which is incompatible with Postgres namespacing.
> Also, we'd want any "package" concept to be usable with external PLs as
> well as PL/pgSQL, which necessitates other Oracle-incompatible changes.
>
> --
> Josh Berkus
> PostgreSQL Experts Inc.
> http://pgexperts.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] Pg_upgrade and toast tables bug discovered

[Bruce Momjian]

On Thu, Sep  4, 2014 at 11:37:27AM -0600, Noah Yetter wrote:
> The 9.3.5 release notes contain...
> 
> 
>   • Fix pg_upgrade for cases where the new server creates a TOAST table but 
> the
> old version did not (Bruce Momjian)
> 
> This rare situation would manifest as "relation OID mismatch" errors.
> 
> 
> ...which I thought was this bug, hence my confusion.  If anyone else is
> experiencing this bug, they may erroneously be led to believe that 9.3.5
> contains the fix.
> 
> 
> I will attempt to build 9.3 stable head and retry my upgrade.

Yes, please let us know.  The post-9.3.5 fix is for the reverse case,
where the new cluster needs a TOAST table that the old cluster didn't.

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

  + Everyone has their own god. +


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

[Josh Berkus]

On 09/04/2014 09:02 AM, Craig Ringer wrote:
> There are a few things I would like to see, like secure session
> variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
> compatibility" seems to be something that comes up before the speaker
> has really understood what that would mean, and the sheer scope of the
> endeavour.
> 
> It's not going from 50% compatible to 80% compatible, it's going from 5%
> compatible to 7% compatible. The most used 5% maybe, but still...

However, there are users who want certain features from PL/SQL not for
compatibility but because they're useful.  For example:

* A "package" concept for encapsulation of multiple procedures, session
variables, etc.

* The ability to "compile" functions/procedures for faster execution.

* Autonomous transactions

We'd also like to borrow stuff from other DBMSes, such as multisets.
All of the above are worth implementing, even if it means implementing
them with different syntax (and mechanics) than PL/SQL.

Sadly, what's prevented us from having "packages" already has been the
insistence of potential feature sponsors that they work *exactly* like
PL/SQL's packages, which is incompatible with Postgres namespacing.
Also, we'd want any "package" concept to be usable with external PLs as
well as PL/pgSQL, which necessitates other Oracle-incompatible changes.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] B-Tree support function number 3 (strxfrm() optimization)

[Peter Geoghegan]

On Thu, Sep 4, 2014 at 9:19 AM, Robert Haas  wrote:
> On Tue, Sep 2, 2014 at 10:27 PM, Peter Geoghegan  wrote:
>> * Still doesn't address the open question of whether or not we should
>> optimistically always try "memcmp() == 0" on tiebreak. I still lean
>> towards "yes".
>
> Let m be the cost of a memcmp() that fails near the end of the
> strings; and let s be the cost of a strcoll that does likewise.
> Clearly s > m.  But approximately what is s/m on platforms where you
> can test?  Say, with 100 byte string, in a few different locales.

Just to be clear: I imagine you're more or less sold on the idea of
testing equality in the event of a tie-break, where the leading 8
primary weight bytes are already known to be equal (and the full text
string lengths also match); the theory of operation behind testing how
good a proxy for full key cardinality abbreviated key cardinality is
is very much predicated on that. We can still win big with very low
cardinality sets this way, which are an important case. What I
consider an open question is whether or not we should do that on the
first call when there is no abbreviated comparison, such as on the
second or subsequent attribute in a multi-column sort, in the hope
that equality will just happen to be indicated.

> If for example s/m > 100 then it's a no-brainer, because in the worst
> case we're adding 1% overhead, and in the best case we're saving 99%.
> OTOH, if s/m < 2 then I almost certainly wouldn't do it, because in
> the worst case we're adding >50% overhead, and in the best case we're
> saving <50%.  That seems like it's doubling down on the abbreviated
> key stuff to work mostly all the time, and I'm not prepared to make
> that bet.  There is of course a lot of daylight between a 2-to-1 ratio
> and a 100-to-1 ratio and I expect the real value is somewhere in the
> middle (probably closer to 2); I haven't at this time made up my mind
> what value would make this worthwhile, but I'd like to know what the
> real numbers are.

Well, we can only lose when the strings happen to be the same size. So
that's something. But I'm willing to consider the possibility that the
memcmp() is virtually free. I would only proceed with this extra
optimization if that is actually the case. Modern CPUs are odd things.
Branch prediction/instruction pipelining, and the fact that we're
frequently stalled on cache misses might combine to make it
effectively the case that the opportunistic memcmp() is free. I could
be wrong about that, and I'm certainly wrong if you test large enough
strings with differences only towards the very end, but it seems
reasonable to speculate that it would work well with appropriate
precautions (in particular, don't do it when the strings are huge).
Let me try and come up with some numbers for a really unsympathetic
case, since you've already seen sympathetic numbers. I think the
sympathetic country/province/city sort test case [1] is actually
fairly representative; sort keys *are* frequently correlated like
that, implying that there are lots of savings to be had by being
"memcmp() == 0 optimistic" when resolving comparisons using the second
or subsequent attribute.

>> * Leaves open the question of what to do when we can't use the
>> abbreviated keys optimization just because a datum tuplesort is
>> preferred when sorting single-attribute tuples (recall that datum case
>> tuplesorts cannot use abbreviated keys). We want to avail of tuplesort
>> datum sorting where we can, except when abbreviated keys are
>> available, which presumably tips the balance in favor of heap tuple
>> sorting even when sorting on only one attribute, simply because then
>> we can then use abbreviated keys. I'm thinking in particular of
>> nodeAgg.c, which is an important case.
>
> I favor leaving this issue to a future patch.  The last thing this
> patch needs is more changes that someone might potentially dislike.
> Let's focus on getting the core thing working, and then you can
> enhance it once we all agree that it is.

Makes sense. I think we should make a separate pass to enable sort
support for B-Tree sorting - that's probably the most compelling case,
after all. That's certainly the thing that I've heard complaints
about. There could be as many as 2-3 follow-up commits.

> On the substance of this issue, I suspect that for pass-by-value data
> types it can hardly be wrong to use the datum tuplesort approach; but
> it's possible we will want to disable it for pass-by-reference data
> types when the abbreviated-key infrastructure is available.  That will
> lose if it turns out that the abbreviated keys aren't capturing enough
> of the entropy, but maybe we'll decide that's OK.  Or maybe not.  But
> I don't think it's imperative that this patch make a change in that
> area, and indeed, in the interest of keeping separate changes
> isolated, I think it's better if it doesn't.

Right. I had presumed that we'd want to figure that out each time. I
wasn't sure how best to go about 

Re: [HACKERS] pgcrypto: PGP armor headers

[Joel Jacobson]

Marko, et al,

This is a review of the pgcrypto PGP Armor Headers patch:
http://www.postgresql.org/message-id/53edcae8.20...@joh.to

Contents & Purpose
==
This patch add functions to create and extract OpenPGP Armor Headers.
from OpenPGP messages.

Included in the patch are updated regression test cases and documentation.

Initial Run
===
The patch applies cleanly to HEAD.

The 144 regression tests all pass successfully against the new patch.

Conclusion
==
Since I'm using these functions in the BankAPI project,
https://github.com/trustly/bankapi, I have tested them
by actually using them in production, in addition to the provided
regression tests, which is a good sign they are working not just
in theory.

+1 for committer review.

On Fri, Aug 15, 2014 at 10:55 AM, Marko Tiikkaja  wrote:
> Hi,
>
>
> On 8/8/14 3:18 PM, I wrote:
>>
>> Currently there's no way to generate or extract armor headers from the
>> PGP armored format in pgcrypto.  I've written a patch to add the
>> support.
>
>
> Latest version of the patch here, having fixed some small coding issues.
>
>
> .marko
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


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

Re: [HACKERS] Pg_upgrade and toast tables bug discovered

[Noah Yetter]

The 9.3.5 release notes contain...


   -

   Fix pg_upgrade for cases where the new server creates a TOAST table but
   the old version did not (Bruce Momjian)

   This rare situation would manifest as "relation OID mismatch" errors.


...which I thought was this bug, hence my confusion.  If anyone else is
experiencing this bug, they may erroneously be led to believe that 9.3.5
contains the fix.


I will attempt to build 9.3 stable head and retry my upgrade.


On Wed, Sep 3, 2014 at 6:03 PM, Bruce Momjian  wrote:

> On Wed, Sep  3, 2014 at 05:12:30PM -0600, Noah Yetter wrote:
> > I'm not sure it's fixed.  I am attempting a pg_upgrade from 9.2.8 to
> 9.3.5 and
> > it dies like so:
> >
> > (...many relations restoring successfully snipped...)
> > pg_restore: creating SEQUENCE address_address_id_seq
> > pg_restore: [archiver (db)] Error while PROCESSING TOC:
> > pg_restore: [archiver (db)] Error from TOC entry 1410; 1259 17670
> SEQUENCE
> > address_address_id_seq javaprod
> > pg_restore: [archiver (db)] could not execute query: ERROR:  could not
> create
> > file "base/16414/17670": File exists
> >
> > Inspecting a copy of the source cluster, OID 17670 does indeed
> correspond to
> > address_address_id_seq, but inspecting the partially-upgraded cluster
> that OID
> > is taken by pg_toast_202359_index.  Again conferring with a copy of the
> source
> > (9.2.8) cluster, the relation corresponding to filenode 202359 does not
> have a
> > toast table.
> >
> > (I know pg-hackers isn't the right place to discuss admin issues, but
> this
> > thread is the only evidence of this bug I can find.  If anyone can
> suggest a
> > workaround I would be infinitely grateful.)
>
> Actually, there was a pg_upgrade fix _after_ the release of 9.3.5 which
> explains this failure:
>
> commit 4c6780fd17aa43ed6362aa682499cc2f9712cc8b
> Author: Bruce Momjian 
> Date:   Thu Aug 7 14:56:13 2014 -0400
>
> pg_upgrade: prevent oid conflicts with new-cluster TOAST tables
>
> Previously, TOAST tables only required in the new cluster
> could cause
> oid conflicts if they were auto-numbered and a later
> conflicting oid had
> to be assigned.
>
> Backpatch through 9.3
>
> Any chance you can download the 9.3.X source tree and try that?  You
> need an entire install, not just a new pg_upgrade binary.  I am
> disapointed I could not fix this before 9.3.5 was released.
>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
>   + Everyone has their own god. +
>

Re: [HACKERS] Display of timestamp in pg_dump custom format

[Bruce Momjian]

On Wed, Sep  3, 2014 at 08:33:31PM -0400, Bruce Momjian wrote:
> I can't seem to find a way to get the timezone offset via C; see:
> 
>   
> http://stackoverflow.com/questions/635780/why-does-glibc-timezone-global-not-agree-with-system-time-on-dst
> 
> On Linux, do 'man timezone' for details.  'timezone' has the non-DST
> offset from GMT, and 'daylight' is a boolean which indicates DST, but
> not how much time is different for DST, and I am not sure it is always
> an hour.  In fact 'daylight' is documented as saying whether there is
> every a daylight savings time, not that DST is active.

Uh, not sure what I was thinking --- strftime() is the way to go.  Here
is the new output:

;
; Archive created at 2014-09-04 13:00:15 -0400   <---
; dbname: test
; TOC Entries: 8
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.5devel
; Dumped by pg_dump version: 9.5devel

I found two other places in our dump code that use strftime with a
similar format, but they had problems with the timezone string on
Windows, so I switched those over to use a numeric timezone offset as
well.

Patch attached.

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

  + Everyone has their own god. +
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
new file mode 100644
index 0018720..ded9135
*** a/src/bin/pg_dump/pg_backup_archiver.c
--- b/src/bin/pg_dump/pg_backup_archiver.c
*** PrintTOCSummary(Archive *AHX, RestoreOpt
*** 964,975 
  	teSection	curSection;
  	OutputContext sav;
  	const char *fmtName;
  
  	sav = SaveOutput(AH);
  	if (ropt->filename)
  		SetOutput(AH, ropt->filename, 0 /* no compression */ );
  
! 	ahprintf(AH, ";\n; Archive created at %s", ctime(&AH->createDate));
  	ahprintf(AH, "; dbname: %s\n; TOC Entries: %d\n; Compression: %d\n",
  			 AH->archdbname, AH->tocCount, AH->compression);
  
--- 964,978 
  	teSection	curSection;
  	OutputContext sav;
  	const char *fmtName;
+ 	struct tm  *tm = localtime(&AH->createDate);
+ 	char		stamp_str[64];
  
  	sav = SaveOutput(AH);
  	if (ropt->filename)
  		SetOutput(AH, ropt->filename, 0 /* no compression */ );
  
! 	strftime(stamp_str, sizeof(stamp_str), "%Y-%m-%d %H:%M:%S %z", tm);
! 	ahprintf(AH, ";\n; Archive created at %s\n", stamp_str);
  	ahprintf(AH, "; dbname: %s\n; TOC Entries: %d\n; Compression: %d\n",
  			 AH->archdbname, AH->tocCount, AH->compression);
  
*** checkSeek(FILE *fp)
*** 3455,3475 
  static void
  dumpTimestamp(ArchiveHandle *AH, const char *msg, time_t tim)
  {
! 	char		buf[256];
  
! 	/*
! 	 * We don't print the timezone on Win32, because the names are long and
! 	 * localized, which means they may contain characters in various random
! 	 * encodings; this has been seen to cause encoding errors when reading the
! 	 * dump script.
! 	 */
! 	if (strftime(buf, sizeof(buf),
! #ifndef WIN32
!  "%Y-%m-%d %H:%M:%S %Z",
! #else
!  "%Y-%m-%d %H:%M:%S",
! #endif
!  localtime(&tim)) != 0)
  		ahprintf(AH, "-- %s %s\n\n", msg, buf);
  }
  
--- 3458,3466 
  static void
  dumpTimestamp(ArchiveHandle *AH, const char *msg, time_t tim)
  {
! 	char		buf[64];
  
! 	if (strftime(buf, sizeof(buf), "%Y-%m-%d %H:%M:%S %z", localtime(&tim)) != 0)
  		ahprintf(AH, "-- %s %s\n\n", msg, buf);
  }
  
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
new file mode 100644
index 4050091..b2b3e6f
*** a/src/bin/pg_dump/pg_dumpall.c
--- b/src/bin/pg_dump/pg_dumpall.c
*** executeCommand(PGconn *conn, const char
*** 2039,2060 
  static void
  dumpTimestamp(char *msg)
  {
! 	char		buf[256];
  	time_t		now = time(NULL);
  
! 	/*
! 	 * We don't print the timezone on Win32, because the names are long and
! 	 * localized, which means they may contain characters in various random
! 	 * encodings; this has been seen to cause encoding errors when reading the
! 	 * dump script.
! 	 */
! 	if (strftime(buf, sizeof(buf),
! #ifndef WIN32
!  "%Y-%m-%d %H:%M:%S %Z",
! #else
!  "%Y-%m-%d %H:%M:%S",
! #endif
!  localtime(&now)) != 0)
  		fprintf(OPF, "-- %s %s\n\n", msg, buf);
  }
  
--- 2039,2048 
  static void
  dumpTimestamp(char *msg)
  {
! 	char		buf[64];
  	time_t		now = time(NULL);
  
! 	if (strftime(buf, sizeof(buf), "%Y-%m-%d %H:%M:%S %z", localtime(&now)) != 0)
  		fprintf(OPF, "-- %s %s\n\n", msg, buf);
  }
  

-- 
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] PQputCopyEnd doesn't adhere to its API contract

[David G Johnston]

On Thu, Sep 4, 2014 at 1:00 PM, Robert Haas [via PostgreSQL] <
ml-node+s1045698n581780...@n5.nabble.com> wrote:

> On Thu, Sep 4, 2014 at 12:53 PM, Bruce Momjian <[hidden email]
> > wrote:
>
> > On Thu, Sep  4, 2014 at 12:52:14PM -0400, Robert Haas wrote:
> >> On Wed, Sep 3, 2014 at 6:24 PM, Bruce Momjian <[hidden email]
> > wrote:
> >> > On Fri, May  9, 2014 at 12:03:36PM -0400, Robert Haas wrote:
> >> >> On Thu, May 8, 2014 at 5:21 PM, Tom Lane <[hidden email]
> > wrote:
> >> >> > Perhaps the text should be like this:
> >> >> >
> >> >> > The result is 1 if the termination message was sent; or in
> nonblocking
> >> >> > mode, this may only indicate that the termination message was
> successfully
> >> >> > queued.  (In nonblocking mode, to be certain that the data has
> been sent,
> >> >> > you should next wait for write-ready and call
> PQflush,
> >> >> > repeating until it returns zero.)  Zero indicates that the
> function could
> >> >> > not queue the termination message because of full buffers; this
> will only
> >> >> > happen in nonblocking mode.  (In this case, wait for write-ready
> and try
> >> >> > the PQputCopyEnd call again.)  If a hard error occurs, -1 is
> returned; you
> >> >> > can use PQerrorMessage to retrieve details.
> >> >>
> >> >> That looks pretty good.   However, I'm realizing this isn't the only
> >> >> place where we probably need to clarify the language.  Just to take
> >> >> one example near at hand, PQputCopyData may also return 1 when it's
> >> >> only queued the data; it seems to try even less hard than
> PQputCopyEnd
> >> >> to ensure that the data is actually sent.
> >> >
> >> > Uh, where are we on this?
> >>
> >> I think someone needs to take Tom's proposed language and make it into
> >> a patch.  And figure out which other functions in the documentation
> >> need similar updates.
> >
> > OK, did David G Johnston email comments from today help here?
>
> I didn't look at them in detail, but they don't seem to match the
> style of our documentation generally.
>
>
​Specific observations would help though that is partly the idea - I've
been more focused on clarity and organization even if it requires deviating
from the current general documentation style.​

​If this is not acceptable I'm happy to incorporate the ideas of others to
try and get the best of both worlds.

David J.
​




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/PQputCopyEnd-doesn-t-adhere-to-its-API-contract-tp5803240p5817812.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On Thu, Sep 4, 2014 at 5:51 PM, Robert Haas  wrote:
>> When you suggest ISAM, that's like saying "demolish your house and
>> build a new one" when all I want is to make small but important
>> changes to what I already do as a professional on a daily basis.
>
> Go right ahead: this is an open source project, after all, and with an
> extremely permissive license to boot.  You can modify your copy of
> PL/pgsql, or clone it and make PL/joelsql and then change whatever you
> like.  Optionally, you could then publish that on PGXN for others to
> use and contribute to.
>
> On the other hand, if what you want is for other people to make
> changes to the official versions of PostgreSQL that are supported and
> maintained by the community, then that's a different thing altogether.
> It entails two challenges: first, to persuade the community that those
> changes will be good for everyone, not just you; and second,
> convincing them that they (rather than you) should be the ones to do
> the work.  So far I'd say you're losing the first argument, and I
> expect you'll lose the second one, too (barring a financial
> transaction, of course).
>
> I'm not trying to brush you off here - I understand your concerns, and
> they're not stupid.  But, like most of the people who have commented,
> I don't agree that your proposals would be an improvement for the
> majority of people.  There are several ways to deal with that, but if
> your goal is to get those changes made in the PostgreSQL community
> then you have to acknowledge the competing concerns to be just as
> valid as your own and come up with a proposal everyone can live with.

If my company would write code in PL/joelsql, I think I would have a hard
time through any technical due diligence in the future. :-)

The main reason why I'm so eager of finding a support from you,
the majority of other readers on this list, is of course because I think
we as a group can come up with a much better solution to the problem
than what I could on my own. And for me it's better if we can agree on
*something* which improves my and others life to *some* extent,
rather than to just sitting here silent waiting another 16 years for
PL/pgSQL 2 to
develop itself.

I can certainly live with a more SQLish syntax than the one I had in mind.

I'm less concerned about the verbosity of the language, if I wanted a condensed
language I should have opted for some other language in the first place,
so that's not my problem.


-- 
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] postgresql latency & bgwriter not doing its job

[Robert Haas]

On Thu, Sep 4, 2014 at 3:09 AM, Ants Aasma  wrote:
> On Thu, Sep 4, 2014 at 12:36 AM, Andres Freund  wrote:
>> It's imo quite clearly better to keep it allocated. For one after
>> postmaster started the checkpointer successfully you don't need to be
>> worried about later failures to allocate memory if you allocate it once
>> (unless the checkpointer FATALs out which should be exceedingly rare -
>> we're catching ERRORs). It's much much more likely to succeed
>> initially. Secondly it's not like there's really that much time where no
>> checkpointer isn't running.
>
> In principle you could do the sort with the full sized array and then
> compress it to a list of buffer IDs that need to be written out. This
> way most of the time you only need a small array and the large array
> is only needed for a fraction of a second.

It's not the size of the array that's the problem; it's the size of
the detonation when the allocation fails.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] PQputCopyEnd doesn't adhere to its API contract

[Robert Haas]

On Thu, Sep 4, 2014 at 12:53 PM, Bruce Momjian  wrote:
> On Thu, Sep  4, 2014 at 12:52:14PM -0400, Robert Haas wrote:
>> On Wed, Sep 3, 2014 at 6:24 PM, Bruce Momjian  wrote:
>> > On Fri, May  9, 2014 at 12:03:36PM -0400, Robert Haas wrote:
>> >> On Thu, May 8, 2014 at 5:21 PM, Tom Lane  wrote:
>> >> > Perhaps the text should be like this:
>> >> >
>> >> > The result is 1 if the termination message was sent; or in nonblocking
>> >> > mode, this may only indicate that the termination message was 
>> >> > successfully
>> >> > queued.  (In nonblocking mode, to be certain that the data has been 
>> >> > sent,
>> >> > you should next wait for write-ready and call PQflush,
>> >> > repeating until it returns zero.)  Zero indicates that the function 
>> >> > could
>> >> > not queue the termination message because of full buffers; this will 
>> >> > only
>> >> > happen in nonblocking mode.  (In this case, wait for write-ready and try
>> >> > the PQputCopyEnd call again.)  If a hard error occurs, -1 is returned; 
>> >> > you
>> >> > can use PQerrorMessage to retrieve details.
>> >>
>> >> That looks pretty good.   However, I'm realizing this isn't the only
>> >> place where we probably need to clarify the language.  Just to take
>> >> one example near at hand, PQputCopyData may also return 1 when it's
>> >> only queued the data; it seems to try even less hard than PQputCopyEnd
>> >> to ensure that the data is actually sent.
>> >
>> > Uh, where are we on this?
>>
>> I think someone needs to take Tom's proposed language and make it into
>> a patch.  And figure out which other functions in the documentation
>> need similar updates.
>
> OK, did David G Johnston email comments from today help here?

I didn't look at them in detail, but they don't seem to match the
style of our documentation generally.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] PQputCopyEnd doesn't adhere to its API contract

[Bruce Momjian]

On Thu, Sep  4, 2014 at 12:52:14PM -0400, Robert Haas wrote:
> On Wed, Sep 3, 2014 at 6:24 PM, Bruce Momjian  wrote:
> > On Fri, May  9, 2014 at 12:03:36PM -0400, Robert Haas wrote:
> >> On Thu, May 8, 2014 at 5:21 PM, Tom Lane  wrote:
> >> > Perhaps the text should be like this:
> >> >
> >> > The result is 1 if the termination message was sent; or in nonblocking
> >> > mode, this may only indicate that the termination message was 
> >> > successfully
> >> > queued.  (In nonblocking mode, to be certain that the data has been sent,
> >> > you should next wait for write-ready and call PQflush,
> >> > repeating until it returns zero.)  Zero indicates that the function could
> >> > not queue the termination message because of full buffers; this will only
> >> > happen in nonblocking mode.  (In this case, wait for write-ready and try
> >> > the PQputCopyEnd call again.)  If a hard error occurs, -1 is returned; 
> >> > you
> >> > can use PQerrorMessage to retrieve details.
> >>
> >> That looks pretty good.   However, I'm realizing this isn't the only
> >> place where we probably need to clarify the language.  Just to take
> >> one example near at hand, PQputCopyData may also return 1 when it's
> >> only queued the data; it seems to try even less hard than PQputCopyEnd
> >> to ensure that the data is actually sent.
> >
> > Uh, where are we on this?
> 
> I think someone needs to take Tom's proposed language and make it into
> a patch.  And figure out which other functions in the documentation
> need similar updates.

OK, did David G Johnston email comments from today help here?

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

  + Everyone has their own god. +


-- 
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] PQputCopyEnd doesn't adhere to its API contract

[Robert Haas]

On Wed, Sep 3, 2014 at 6:24 PM, Bruce Momjian  wrote:
> On Fri, May  9, 2014 at 12:03:36PM -0400, Robert Haas wrote:
>> On Thu, May 8, 2014 at 5:21 PM, Tom Lane  wrote:
>> > Perhaps the text should be like this:
>> >
>> > The result is 1 if the termination message was sent; or in nonblocking
>> > mode, this may only indicate that the termination message was successfully
>> > queued.  (In nonblocking mode, to be certain that the data has been sent,
>> > you should next wait for write-ready and call PQflush,
>> > repeating until it returns zero.)  Zero indicates that the function could
>> > not queue the termination message because of full buffers; this will only
>> > happen in nonblocking mode.  (In this case, wait for write-ready and try
>> > the PQputCopyEnd call again.)  If a hard error occurs, -1 is returned; you
>> > can use PQerrorMessage to retrieve details.
>>
>> That looks pretty good.   However, I'm realizing this isn't the only
>> place where we probably need to clarify the language.  Just to take
>> one example near at hand, PQputCopyData may also return 1 when it's
>> only queued the data; it seems to try even less hard than PQputCopyEnd
>> to ensure that the data is actually sent.
>
> Uh, where are we on this?

I think someone needs to take Tom's proposed language and make it into
a patch.  And figure out which other functions in the documentation
need similar updates.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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 1.2

[Pavel Stehule]

2014-09-04 18:02 GMT+02:00 Kevin Grittner :

> Pavel Stehule  wrote:
>
> > You just need a ISAM API for Postgres, That is all.
>
> Joel sure hasn't *shown* us anything to suggest that wouldn't
> answer his needs better than any PL, or explained why that wouldn't
> be a better solution for him.
>

I understand what Joel does. And there is a space for improvement of
plpgsql - on syntax level, on internal level. But we can start with some
less controversial.

And some controversial points we can coverage by extensions. It is in
conformance with Postgres community politics - where is not agreement, use
extensions. We have to be able to write these extensions.

Extensibility of plpgsql is on the begin. But for some special use cases,
these extensions can be perfect.

>From this long discuss I am thinking so there is perfect agreement on
plpgsql asserts. We needed. And now we know where assertations can be used.
There is agreement on using binary casting instead IO casting every where
where it is possible. And I am not against to ensuring consistent behave of
assigning, returning from fce for composite types. There is small
differences between rows, records, .. But should not be too hurry. There
are only few people who would to changes in this area. Almost users are
happy.

Personally I would to see a discussion about enhancing SPI much more --
because it is base of all PL and some performance limits and some internal
complexity of plpgsql (and plpgsql_check too) is based on missing some
interface between SPI and PL.

Regards

Pavel


>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

Re: [HACKERS] B-Tree support function number 3 (strxfrm() optimization)

[Robert Haas]

On Wed, Sep 3, 2014 at 5:44 PM, Peter Geoghegan  wrote:
> On Wed, Sep 3, 2014 at 2:18 PM, Robert Haas  wrote:
>> My suggestion is to remove the special cases for Darwin and 32-bit
>> systems and see how it goes.
>
> I guess it should still be a configure option, then. Or maybe there
> should just be a USE_ABBREV_KEYS macro within pg_config_manual.h.
>
> Are you suggesting that the patch be committed with the optimization
> enabled on all platforms by default, with the option to revisit
> disabling it if and when there is user push-back? I don't think that's
> unreasonable, given the precautions now taken, but I'm just not sure
> that's what you mean.

That's what I mean.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] B-Tree support function number 3 (strxfrm() optimization)

[Robert Haas]

On Tue, Sep 2, 2014 at 10:27 PM, Peter Geoghegan  wrote:
> * Still doesn't address the open question of whether or not we should
> optimistically always try "memcmp() == 0" on tiebreak. I still lean
> towards "yes".

Let m be the cost of a memcmp() that fails near the end of the
strings; and let s be the cost of a strcoll that does likewise.
Clearly s > m.  But approximately what is s/m on platforms where you
can test?  Say, with 100 byte string, in a few different locales.

If for example s/m > 100 then it's a no-brainer, because in the worst
case we're adding 1% overhead, and in the best case we're saving 99%.
OTOH, if s/m < 2 then I almost certainly wouldn't do it, because in
the worst case we're adding >50% overhead, and in the best case we're
saving <50%.  That seems like it's doubling down on the abbreviated
key stuff to work mostly all the time, and I'm not prepared to make
that bet.  There is of course a lot of daylight between a 2-to-1 ratio
and a 100-to-1 ratio and I expect the real value is somewhere in the
middle (probably closer to 2); I haven't at this time made up my mind
what value would make this worthwhile, but I'd like to know what the
real numbers are.

> * Leaves open the question of what to do when we can't use the
> abbreviated keys optimization just because a datum tuplesort is
> preferred when sorting single-attribute tuples (recall that datum case
> tuplesorts cannot use abbreviated keys). We want to avail of tuplesort
> datum sorting where we can, except when abbreviated keys are
> available, which presumably tips the balance in favor of heap tuple
> sorting even when sorting on only one attribute, simply because then
> we can then use abbreviated keys. I'm thinking in particular of
> nodeAgg.c, which is an important case.

I favor leaving this issue to a future patch.  The last thing this
patch needs is more changes that someone might potentially dislike.
Let's focus on getting the core thing working, and then you can
enhance it once we all agree that it is.

On the substance of this issue, I suspect that for pass-by-value data
types it can hardly be wrong to use the datum tuplesort approach; but
it's possible we will want to disable it for pass-by-reference data
types when the abbreviated-key infrastructure is available.  That will
lose if it turns out that the abbreviated keys aren't capturing enough
of the entropy, but maybe we'll decide that's OK.  Or maybe not.  But
I don't think it's imperative that this patch make a change in that
area, and indeed, in the interest of keeping separate changes
isolated, I think it's better if it doesn't.

> There are still FIXME/TODO comments for each of these two points.
> Further, this revised/rebased patch set:
>
> * Incorporates your feedback on stylistic issues, with changes
> confined to their own commit (on top of earlier commits that are
> almost, but not quite, the same as the prior revision that your
> remarks apply to).
>
> * No longer does anything special within reversedirection_heap(),
> since that is unnecessary, as it's only used by bounded sorts, which
> aren't a useful target for abbreviated keys. This is noted. There is
> no convenient point to add a defensive assertion against this, so I
> haven't.
>
> * Updates comments in master in a broken-out way, reflecting opclass
> contract with sortsupport as established by
> 1d41739e5a04b0e93304d24d864b6bfa3efc45f2, that is convenient to apply
> to and commit in the master branch immediately.

Thanks, committed that one.  The remaining patches can be squashed
into a single one, as none of them can be applied without the others.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Join push-down support for foreign tables

[Atri Sharma]

On Thu, Sep 4, 2014 at 9:33 PM, Bruce Momjian  wrote:

> On Thu, Sep  4, 2014 at 09:31:20PM +0530, Atri Sharma wrote:
> > I am thinking we would eventually have to cache the statistics, then
> get
> > some kind of invalidation message from the foreign server.  I am also
> > thinking that cache would have to be global across all backends, I
> guess
> > similar to our invalidation cache.
> >
> >
> >
> >
> > That could lead to some bloat in storing statistics since we may have a
> lot of
> > tables for a lot of foreign servers. Also, will we have VACUUM look at
> > ANALYZING the foreign tables?
>
> > Also, how will we decide that the statistics are invalid? Will we have
> the FDW
> > query the foreign server and do some sort of comparison between the
> statistics
> > the foreign server has and the statistics we locally have? I am trying to
> > understand how the idea of invalidation message from foreign server will
> work.
>
> Well, ANALYZING is running on the foreign server, and somehow it would
> be nice if it would send a message to us about its new statistics, or we
> can do it like http does and it gives us a last-refresh statistics date
> when we connect.
>

Not sure how that would work without changing the way ANALYZE works on the
foreign server. http idea could work,though.

>
> I am not sure how it will work --- I am just suspecting that we might
> get to a point where the statistics lookup overhead on the foreign
> server might become a bottleneck.
>

Totally agree, but doing the planning only locally opens the questions I
mentioned above, and also deprives the foreign server database to do any
optimizations that it may want to do (assuming that the foreign database
and postgres query planner do not generate identical plans). This is only
my thought though, we could also be planning better than the foreign server
database, so the optimization part I raised is debatable.

Regards,

Atri




-- 
Regards,

Atri
*l'apprenant*

Re: [HACKERS] pgcrypto: PGP signatures

[Joel Jacobson]

Marko, et al,

This is a review of the pgcrypto PGP signatures patch:
http://www.postgresql.org/message-id/53edbcf0.9070...@joh.to

There hasn't been any discussion, at least that I've been able to find.

Contents & Purpose
==
This patch add functions to create, verify and extract infromation
from OpenPGP signatures. Previously pgcrypto only peformed
PGP encrypt/decrypt, not sign/verify. This is a painful limitation
since a very common use-case for OpenPGP is the signature-part,
where two parties want to verify messages originate from each other,
and not only encrypt the messages.

Included in the patch are updated regression test cases and documentation.

Initial Run
===
The patch applies cleanly to HEAD after changing a single line in the patch:
< ! Giving this function a secret key will produce an error.
---
> ! Giving this function a secret key will produce a error.
This grammar fix was already fixed in 05258761bf12a64befc9caec1947b254cdeb74c5,
and therefore caused the conflict.

The 144 regression tests all pass successfully against the new patch.

Conclusion
==
Since I'm using these functions in the BankAPI project,
https://github.com/trustly/bankapi, I have tested them
by actually using them in production, in addition to the provided
regression tests, which is a good sign they are working not just
in theory.

+1 for committer review after the changes suggested by Jeff Janes and
Thomas Munro.


On Fri, Aug 15, 2014 at 9:55 AM, Marko Tiikkaja  wrote:
> Hi,
>
>
> On 8/7/14 12:15 PM, I wrote:
>>
>> Here's v2 of the patch.  I've changed the info-extracting code to not
>> look for signatures beyond the data, which also meant that it had to
>> parse one-pass signatures (which it didn't do before).  This matches the
>> behaviour of the main decryption code.
>
>
> Here's the latest version where I've added the option to extract the
> creation time from the signatures.
>
>
>
> .marko
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>


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

Hi Craig


2014-09-04 17:54 GMT+02:00 Craig Ringer :

> On 09/04/2014 02:48 AM, Robert Haas wrote:
> > To take another example, I've been complaining about the fact
> > that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> > than any other database I'm aware of for years, probably since before
> > I joined EnterpriseDB.
>
> +10
>
> This still drives me nuts, and it's a serious problem for ORM users too.
>
> The idea that we won't accept a 'text' typed input for an 'xml' or
> 'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
> an argument for what problems this solves.
>
> I know why the changes in 8.3 were made, and they're clearly beneficial
> overall, but we need to start putting some more implicit casts from text
> to text-like types in, especially where there's no SQL-standard type
> that users of JDBC etc can easily use in mappings.
>

I don't see a problem in additional casts.

But some missing casts are well - I found lot performance issues based on
using wrong data types - integers, dates in text column.

Pavel


>
> --
>  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] Join push-down support for foreign tables

[Bruce Momjian]

On Thu, Sep  4, 2014 at 09:31:20PM +0530, Atri Sharma wrote:
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server.  I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.
> 
> 
> 
> 
> That could lead to some bloat in storing statistics since we may have a lot of
> tables for a lot of foreign servers. Also, will we have VACUUM look at
> ANALYZING the foreign tables?

> Also, how will we decide that the statistics are invalid? Will we have the FDW
> query the foreign server and do some sort of comparison between the statistics
> the foreign server has and the statistics we locally have? I am trying to
> understand how the idea of invalidation message from foreign server will work.

Well, ANALYZING is running on the foreign server, and somehow it would
be nice if it would send a message to us about its new statistics, or we
can do it like http does and it gives us a last-refresh statistics date
when we connect.

I am not sure how it will work --- I am just suspecting that we might
get to a point where the statistics lookup overhead on the foreign
server might become a bottleneck.

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

  + Everyone has their own god. +


-- 
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/04/2014 06:48 AM, Joshua D. Drake wrote:
> 
> On 09/03/2014 11:48 AM, Robert Haas wrote:
> 
>> Anyway, to get back around to the topic of PL/SQL compatibility
>> specifically, if you care about that issue, pick one thing that exists
>> in PL/SQL but not in PL/pgsql and try to do something about it.  Maybe
>> it'll be something that EnterpiseDB has already done something about,
>> in which case, if your patch gets committed, Advanced Server will lose
>> a bit of distinction as compared with PostgreSQL.  Or maybe it'll be
>> something that EnterpriseDB hasn't done anything about, and then
>> everybody comes out strictly ahead.  What I think you shouldn't do
>> (although you're free to ignore me) is continue thinking of Oracle
>> compatibility as one monolithic thing, because it isn't, or to pursue
>> of a course of trying to get the PostgreSQL community to slavishly
>> follow Oracle, because I think you'll fail, and even if you succeed I
>> don't think the results will actually be positive for PostgreSQL.
> 
> Well put Robert.

Indeed, especially with reference to the size and scope of Oracle. Its
XML library alone is huge.

At best it's reasonable to hope for compatibility with a limited subset
of PL/SQL - and really, we're a good way there already, with most of
what's missing being down to missing core server features or things
PostgreSQL just does differently.

True "Oracle compatibility" (for procedures) pretty much requires an
embedded JVM with a rich class library. Since PL/Java seems to be dying
a slow death by neglect and disinterest I don't think it's likely anyone
would be tackling compatibility with the embedded JVM features anytime soon.

There are a few things I would like to see, like secure session
variables in PL/PgSQL. Mostly, though, I think talk of "Oracle
compatibility" seems to be something that comes up before the speaker
has really understood what that would mean, and the sheer scope of the
endeavour.

It's not going from 50% compatible to 80% compatible, it's going from 5%
compatible to 7% compatible. The most used 5% maybe, but still...

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

[Marko Tiikkaja]

On 9/4/14 5:54 PM, Craig Ringer wrote:

On 09/04/2014 02:48 AM, Robert Haas wrote:

To take another example, I've been complaining about the fact
that PostgreSQL 8.3+ requires far more typecasts in stored procedures
than any other database I'm aware of for years, probably since before
I joined EnterpriseDB.


+10

This still drives me nuts, and it's a serious problem for ORM users too.

The idea that we won't accept a 'text' typed input for an 'xml' or
'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
an argument for what problems this solves.


In what context?  Are we talking about parameters which have been cast 
to text, or what?  I don't remember ever having an issue with this, 
though I remember the lack of implicit cast from text to json (or the 
other way round) making a bug more obvious a couple of times.



.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 1.2

[Kevin Grittner]

Pavel Stehule  wrote:

> You just need a ISAM API for Postgres, That is all.

Joel sure hasn't *shown* us anything to suggest that wouldn't
answer his needs better than any PL, or explained why that wouldn't
be a better solution for him.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Join push-down support for foreign tables

[Atri Sharma]

On Thu, Sep 4, 2014 at 9:26 PM, Bruce Momjian  wrote:

> On Thu, Sep  4, 2014 at 08:41:43PM +0530, Atri Sharma wrote:
> >
> >
> > On Thursday, September 4, 2014, Bruce Momjian  wrote:
> >
> > On Thu, Sep  4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> > > The main problem I see here is that accurate costing may require a
> > > round-trip to the remote server.  If there is only one path that is
> > > probably OK; the cost of asking the question will usually be more
> than
> > > paid for by hearing that the pushed-down join clobbers the other
> > > possible methods of executing the query.  But if there are many
> paths,
> > > for example because there are multiple sets of useful pathkeys, it
> > > might start to get a bit expensive.
> > >
> > > Probably both the initial cost and final cost calculations should
> be
> > > delegated to the FDW, but maybe within postgres_fdw, the initial
> cost
> > > should do only the work that can be done without contacting the
> remote
> > > server; then, let the final cost step do that if appropriate.  But
> I'm
> > > not entirely sure what is best here.
> >
> > I am thinking eventually we will need to cache the foreign server
> > statistics on the local server.
> >
> >
> >
> >
> > Wouldn't that lead to issues where the statistics get outdated and we
> have to
> > anyways query the foreign server before planning any joins? Or are you
> thinking
> > of dropping the foreign table statistics once the foreign join is
> complete?
>
> I am thinking we would eventually have to cache the statistics, then get
> some kind of invalidation message from the foreign server.  I am also
> thinking that cache would have to be global across all backends, I guess
> similar to our invalidation cache.
>
>
>
That could lead to some bloat in storing statistics since we may have a lot
of tables for a lot of foreign servers. Also, will we have VACUUM look at
ANALYZING the foreign tables?

Also, how will we decide that the statistics are invalid? Will we have the
FDW query the foreign server and do some sort of comparison between the
statistics the foreign server has and the statistics we locally have? I am
trying to understand how the idea of invalidation message from foreign
server will work.

Regards,

Atri

Re: [HACKERS] Join push-down support for foreign tables

[Bruce Momjian]

On Thu, Sep  4, 2014 at 08:41:43PM +0530, Atri Sharma wrote:
> 
> 
> On Thursday, September 4, 2014, Bruce Momjian  wrote:
> 
> On Thu, Sep  4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> > The main problem I see here is that accurate costing may require a
> > round-trip to the remote server.  If there is only one path that is
> > probably OK; the cost of asking the question will usually be more than
> > paid for by hearing that the pushed-down join clobbers the other
> > possible methods of executing the query.  But if there are many paths,
> > for example because there are multiple sets of useful pathkeys, it
> > might start to get a bit expensive.
> >
> > Probably both the initial cost and final cost calculations should be
> > delegated to the FDW, but maybe within postgres_fdw, the initial cost
> > should do only the work that can be done without contacting the remote
> > server; then, let the final cost step do that if appropriate.  But I'm
> > not entirely sure what is best here.
> 
> I am thinking eventually we will need to cache the foreign server
> statistics on the local server.
> 
> 
> 
> 
> Wouldn't that lead to issues where the statistics get outdated and we have to
> anyways query the foreign server before planning any joins? Or are you 
> thinking
> of dropping the foreign table statistics once the foreign join is complete?

I am thinking we would eventually have to cache the statistics, then get
some kind of invalidation message from the foreign server.  I am also
thinking that cache would have to be global across all backends, I guess
similar to our invalidation cache.

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

  + Everyone has their own god. +


-- 
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/04/2014 02:48 AM, Robert Haas wrote:
> To take another example, I've been complaining about the fact
> that PostgreSQL 8.3+ requires far more typecasts in stored procedures
> than any other database I'm aware of for years, probably since before
> I joined EnterpriseDB.

+10

This still drives me nuts, and it's a serious problem for ORM users too.

The idea that we won't accept a 'text' typed input for an 'xml' or
'json' field is IMO absurdly and needlessly pedantic. I've not yet seen
an argument for what problems this solves.

I know why the changes in 8.3 were made, and they're clearly beneficial
overall, but we need to start putting some more implicit casts from text
to text-like types in, especially where there's no SQL-standard type
that users of JDBC etc can easily use in mappings.

-- 
 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 1.2

[Robert Haas]

On Thu, Sep 4, 2014 at 11:32 AM, Joel Jacobson  wrote:
>> On 4 sep 2014, at 17:18, Pavel Stehule  wrote:
>>
>> You just need a ISAM API for Postgres, That is all.
>
> Now you are being ironic, and I would prefer to keep the discussion on
> a serious level. You know that's not applicable in my case, you know
> what I do for work and what kind of system we already have.
>
> I *love* plpgsql and our development method. I just want it to get
> slightly more convenient and secure.
>
> When you suggest ISAM, that's like saying "demolish your house and
> build a new one" when all I want is to make small but important
> changes to what I already do as a professional on a daily basis.

Go right ahead: this is an open source project, after all, and with an
extremely permissive license to boot.  You can modify your copy of
PL/pgsql, or clone it and make PL/joelsql and then change whatever you
like.  Optionally, you could then publish that on PGXN for others to
use and contribute to.

On the other hand, if what you want is for other people to make
changes to the official versions of PostgreSQL that are supported and
maintained by the community, then that's a different thing altogether.
It entails two challenges: first, to persuade the community that those
changes will be good for everyone, not just you; and second,
convincing them that they (rather than you) should be the ones to do
the work.  So far I'd say you're losing the first argument, and I
expect you'll lose the second one, too (barring a financial
transaction, of course).

I'm not trying to brush you off here - I understand your concerns, and
they're not stupid.  But, like most of the people who have commented,
I don't agree that your proposals would be an improvement for the
majority of people.  There are several ways to deal with that, but if
your goal is to get those changes made in the PostgreSQL community
then you have to acknowledge the competing concerns to be just as
valid as your own and come up with a proposal everyone can live with.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[HACKERS] .ready files appearing on slaves

[Jehan-Guillaume de Rorthais]

Hi hackers,

Since few months, we occasionally see .ready files appearing on some slave
 instances from various context. The two I have in mind are under 9.2.x.

I tried to investigate a bit. These .ready files are created when a WAL file
from pg_xlog has no corresponding file in pg_xlog/archive_status. I could
easily experience this by deleting such a file: it is created again at the next
restartpoint or checkpoint received from the master.

Looking at the WAL in pg_xlog folder corresponding to these .ready files, they
are all much older than the current WAL "cycle" in both mtime and name logic
sequence. As instance on one of these box we have currently 6 of those "ghost"
WALs:

  00021E5300FF
  00021F1800FF
  0002204700FF
  000220BF00FF
  0002214000FF
  0002237000FF
  0002255D00A8
  0002255D00A9
  [...normal WAL sequence...]
  0002255E009D

And on another box:

  0001040E00FF
  0001041400DA
  0001046E00FF
  0001047000FF
  00010485000F
  000104850010
  [...normal WAL sequence...]
  000104850052

So it seems for some reasons, these old WALs were "forgotten" by the
restartpoint mechanism when they should have been recylced/deleted. 

For one of these servers, I could correlate this with some brutal disconnection
of the streaming replication appearing in its logs. But there was no known SR
disconnection on the second one.

Any idea about this weird behaviour? What can we do to help you investigate
further?

Regards,
-- 
Jehan-Guillaume de Rorthais
Dalibo
http://www.dalibo.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 1.2

[Jan Wieck]

On 09/04/2014 11:16 AM, Joel Jacobson wrote:

On 4 sep 2014, at 16:45, Hannu Krosing  wrote:

When looking from the other end of the problem, we are
using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
when we really want scalars.

My understanding is that one main drivers of starting this thread
was wanting also guaranteed SCALAR versions of these.

And wanting them in a way that is easy to use.


+1

Thank you! I have been trying to explain this in multiple cryptic ways
but failed. You just nailed it! That's *exactly* what I mean!


I believe we all agree that the availability of most of the proposed 
functionality is desirable.


I think the main difference between your point of view and that of a few 
others (me included) is that you prefer a language that is easy and fast 
to type, with as few key strokes as possible, while we prefer a language 
that is similar to SQL, which is rather verbose to the reader. At least 
when the discussion is about the default procedural language installed 
with the core database system.


Such a language should be as similar as possible to SQL. Which is the 
reason why I believe that the CHECK clause belongs into the main parser, 
not into the PL.



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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 1.2

[Joel Jacobson]

> On 4 sep 2014, at 17:18, Pavel Stehule  wrote:
>
> You just need a ISAM API for Postgres, That is all.

Now you are being ironic, and I would prefer to keep the discussion on
a serious level. You know that's not applicable in my case, you know
what I do for work and what kind of system we already have.

I *love* plpgsql and our development method. I just want it to get
slightly more convenient and secure.

When you suggest ISAM, that's like saying "demolish your house and
build a new one" when all I want is to make small but important
changes to what I already do as a professional on a daily basis.


-- 
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 1.2

[Pavel Stehule]

2014-09-04 17:10 GMT+02:00 Joel Jacobson :

>
>
> On 4 sep 2014, at 15:32, Pavel Stehule  wrote:
>
>
>
>
> 2014-09-04 15:24 GMT+02:00 Jan Wieck :
>
>> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>>
>>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing >> A more SQL-ish way of doing the same could probably be called COMMAND
>>> CONSTRAINTS
>>> and look something like this
>>>
>>> SELECT
>>> ...
>>> CHECK (ROWCOUNT BETWEEN 0 AND 1);
>>>
>>>
>>> It is very near to my proposed ASSERT
>>>
>>
>> Only if the ASSERT syntax would become part of the original statement, it
>> is supposed to check. In Hannu's command constraint example above, the
>> statement that causes the error, and thus will be logged and become
>> identified by the error message, is the actual SELECT (or other DML
>> statement).
>>
>
> this is valid argument.
>
> On second hand, I proposed a ASSERT that was not based on expressions
> only. There is not a technical issue to write assert with knowledge of
> related statement.
>
>
>>
>> I think I like the COMMAND CONSTRAINT the best so far.
>>
>
> I not, because when it will not be part of SQL, than parser in plpgsql
> will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE
>
>
> This is what I suspected. You are against the best syntax because they are
> more complex to implement. I think that's coming into the discussion from
> the wrong direction. First agree on the best syntax, then worry about the
> implementation.
>
>
Nobody say here, so it is best syntax. It is request of proprietary
enhancing of SQL and lot of people say strongly no. But you don't listen.


> I also understand the syntax changes will mean a lot of trouble for your
> plpgsql_check_function() project, but that cannot hold us back, we must aim
> for the best possible syntax with plpgsql2.
>  Your work with plpgsql_check_function() btw saved me hundreds of hours of
> work, when we upgraded from 8.4 a few years ago, many thanks Pavel!
>

I have no problem with plpgsql_check_function management. I remember well
how issues is related to support plpgsql specific STRICT or INTO clauses.

Pavel




>
>
> Pavel
>
>
>>
>>
>> Regards,
>> Jan
>>
>> --
>> Jan Wieck
>> Senior Software Engineer
>> http://slony.info
>>
>
>

Re: [HACKERS] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 17:16 GMT+02:00 Joel Jacobson :

> > On 4 sep 2014, at 16:45, Hannu Krosing  wrote:
> >
> > When looking from the other end of the problem, we are
> > using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
> > when we really want scalars.
> >
> > My understanding is that one main drivers of starting this thread
> > was wanting also guaranteed SCALAR versions of these.
> >
> > And wanting them in a way that is easy to use.
>
> +1
>
> Thank you! I have been trying to explain this in multiple cryptic ways
> but failed. You just nailed it! That's *exactly* what I mean!
>

You just need a ISAM API for Postgres, That is all.

Pavel


>
> Thanks for clarifying!
>
> >
> >
> > Cheers
> >
> >
> > --
> > Hannu Krosing
> > PostgreSQL Consultant
> > Performance, Scalability and High Availability
> > 2ndQuadrant Nordic OÜ
>

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

> On 4 sep 2014, at 16:45, Hannu Krosing  wrote:
>
> When looking from the other end of the problem, we are
> using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
> when we really want scalars.
>
> My understanding is that one main drivers of starting this thread
> was wanting also guaranteed SCALAR versions of these.
>
> And wanting them in a way that is easy to use.

+1

Thank you! I have been trying to explain this in multiple cryptic ways
but failed. You just nailed it! That's *exactly* what I mean!

Thanks for clarifying!

>
>
> 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] Join push-down support for foreign tables

[Atri Sharma]

On Thursday, September 4, 2014, Bruce Momjian  wrote:

> On Thu, Sep  4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> > The main problem I see here is that accurate costing may require a
> > round-trip to the remote server.  If there is only one path that is
> > probably OK; the cost of asking the question will usually be more than
> > paid for by hearing that the pushed-down join clobbers the other
> > possible methods of executing the query.  But if there are many paths,
> > for example because there are multiple sets of useful pathkeys, it
> > might start to get a bit expensive.
> >
> > Probably both the initial cost and final cost calculations should be
> > delegated to the FDW, but maybe within postgres_fdw, the initial cost
> > should do only the work that can be done without contacting the remote
> > server; then, let the final cost step do that if appropriate.  But I'm
> > not entirely sure what is best here.
>
> I am thinking eventually we will need to cache the foreign server
> statistics on the local server.
>
>
>
Wouldn't that lead to issues where the statistics get outdated and we have
to anyways query the foreign server before planning any joins? Or are you
thinking of dropping the foreign table statistics once the foreign join is
complete?

Regards,

Atri


-- 
Regards,

Atri
*l'apprenant*

Re: [HACKERS] PL/pgSQL 1.2

[Joel Jacobson]

On 4 sep 2014, at 15:32, Pavel Stehule  wrote:




2014-09-04 15:24 GMT+02:00 Jan Wieck :

> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>
>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing > A more SQL-ish way of doing the same could probably be called COMMAND
>> CONSTRAINTS
>> and look something like this
>>
>> SELECT
>> ...
>> CHECK (ROWCOUNT BETWEEN 0 AND 1);
>>
>>
>> It is very near to my proposed ASSERT
>>
>
> Only if the ASSERT syntax would become part of the original statement, it
> is supposed to check. In Hannu's command constraint example above, the
> statement that causes the error, and thus will be logged and become
> identified by the error message, is the actual SELECT (or other DML
> statement).
>

this is valid argument.

On second hand, I proposed a ASSERT that was not based on expressions only.
There is not a technical issue to write assert with knowledge of related
statement.


>
> I think I like the COMMAND CONSTRAINT the best so far.
>

I not, because when it will not be part of SQL, than parser in plpgsql will
be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE


This is what I suspected. You are against the best syntax because they are
more complex to implement. I think that's coming into the discussion from
the wrong direction. First agree on the best syntax, then worry about the
implementation.

I also understand the syntax changes will mean a lot of trouble for your
plpgsql_check_function() project, but that cannot hold us back, we must aim
for the best possible syntax with plpgsql2.
Your work with plpgsql_check_function() btw saved me hundreds of hours of
work, when we upgraded from 8.4 a few years ago, many thanks Pavel!


Pavel


>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

Re: [HACKERS] Scaling shared buffer eviction

[Alvaro Herrera]

Robert Haas wrote:
> On Wed, Sep 3, 2014 at 7:27 AM, Amit Kapila  wrote:
> >> +Background Reclaimer's Processing
> >> +-
> >>
> >> I suggest titling this section "Background Reclaim".
> >
> > I don't mind changing it, but currently used title is based on similar
> > title "Background Writer's Processing".  It is used in previous
> > paragraph.  Is there a reason to title this differently?
> 
> Oh, I didn't see that.  Seems like weird phrasing to me, but I guess
> it's probably better to keep it consistent.

... or you can also change the other one.

-- 
Álvaro Herrerahttp://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 4 sep 2014, at 15:09, Shaun Thomas  wrote:
>
>> On 09/01/2014 04:04 AM, Joel Jacobson wrote:
>>
>> + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
>> row, as that's the most common use-case, and provide alternative syntax
>> to modify multiple or zero rows.
>
> What? No. The whole point of SQL is that it's set-based and can modify 
> multiple rows at once. Hobbling it specifically for functions seems 
> fundamentally flawed. Especially for what we purport to be a core PostgreSQL 
> language.

I've dropped that suggestion and is instead in favour of a keyword
like STRICT, ONE ROW, SINGLETON or [1] like suggested by others. Any
keyword or syntax will do fine, but I would prefer STRICT.

>
>> + Change all warnings into errors
>
> I... what? I could see coming up with a better exception handling mechanism 
> for escalating messages. But you're talking about taking a core element of 
> PostgreSQL (warnings) and simply ripping them out so plpgsql2 loses even that 
> small functionality.

You misunderstood, I meant plpgsql warnings, that you currently can
turn into errors by setting things in the config file. Such as
shadowing of variables.

>
> I'm sure you've put a lot of thought into this, but you're not the only 
> person using plpgsql or any, however ambitious, potential replacement.
>
> --
> Shaun Thomas
> OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
> 312-676-8870
> stho...@optionshouse.com
>
> __
>
> See http://www.peak6.com/email_disclaimer/ for terms and conditions related 
> to this email


-- 
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}

[Robert Haas]

On Wed, Sep 3, 2014 at 2:13 PM, Peter Geoghegan  wrote:
> On Wed, Sep 3, 2014 at 9:51 AM, Robert Haas  wrote:
>>> Essentially, the implementation has all stages of query processing
>>> During the execution of the parent ModifyTable, a special auxiliary
>>> subquery (the UPDATE ModifyTable) is considered as a special case.
>>> This is not a subplan of the ModifyTable node in the conventional
>>> sense, and so does not appear within EXPLAIN output.
>>
>> ...that sounds wonky.
>
> Which part? It certainly wouldn't be helpful if the (say) auxiliary
> plan's "sequential scan" appeared within EXPLAIN output. That's just
> an implementation detail. Note that the structure of the plan is
> highly restricted, since it needs to be "driven by the insert" (or,
> rather, the insert's conflicts, including conflicts not visible to the
> command's MVCC snapshot). There won't be any interesting variation in
> the plan. Although, that said, the implementation should probably
> display any "Filter: ..." conditions implied by the special UPDATE
> qual.

I think there shouldn't be any plan nodes in the system that don't get
displayed by explain.  If you're using a plan node for something, and
think it shouldn't be displayed by explain, then either (1) you are
wrong or (2) you are abusing the plan node.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Join push-down support for foreign tables

[Bruce Momjian]

On Thu, Sep  4, 2014 at 08:37:08AM -0400, Robert Haas wrote:
> The main problem I see here is that accurate costing may require a
> round-trip to the remote server.  If there is only one path that is
> probably OK; the cost of asking the question will usually be more than
> paid for by hearing that the pushed-down join clobbers the other
> possible methods of executing the query.  But if there are many paths,
> for example because there are multiple sets of useful pathkeys, it
> might start to get a bit expensive.
> 
> Probably both the initial cost and final cost calculations should be
> delegated to the FDW, but maybe within postgres_fdw, the initial cost
> should do only the work that can be done without contacting the remote
> server; then, let the final cost step do that if appropriate.  But I'm
> not entirely sure what is best here.

I am thinking eventually we will need to cache the foreign server
statistics on the local server.

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

  + Everyone has their own god. +


-- 
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:
> I tried your patches against libedit-28.  Wherever a command contains a
> newline, unpatched psql writes the three bytes "\^A" to the history file, and
> patched psql writes the four bytes "\012".  Unpatched psql correctly reads
> either form of the history file.  Patched psql misinterprets a history file
> created by unpatched psql, placing 0x01 bytes in the recalled command where it
> should have newlines.  That's a worrisome compatibility break.

I think you got the test cases backwards, or maybe neglected the aspect
about how unpatched psql will only translate ^J to ^A in the oldest
(or maybe the newest? too pressed for time to recheck right now) history
entry.

The issue is that a patched psql, or a psql with a sufficient old libedit,
will apply ^J -> ^A to all entries when saving, and the reverse when
loading.  Without the patch, only the oldest entry gets transformed.
Failure to reverse the encoding in all lines is what creates a
user-visible problem.  If we do not fix this, that's what we risk.
We do not escape a problem by refusing to fix it.

regards, tom lane


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

Re: [HACKERS] PL/pgSQL 1.2

[Hannu Krosing]

On 09/04/2014 02:40 PM, Pavel Stehule wrote:
>
>
>
> 2014-09-04 14:37 GMT+02:00 Joel Jacobson  >:
>
>
>
> On 4 sep 2014, at 11:42, Pavel Stehule  > wrote:
>> 2014-09-04 11:22 GMT+02:00 Joel Jacobson > >:
>>
>> The point was, RETURNS returns 1 while RETURNS SETOF returns
>> 0 .. n.
>>
>>
>> no RETURNS return "VALUE" (it is not a row) .. and in combination
>> with SELECT - value will be a row. RETURNS SETOF returns rows
>
> I intentionally excluded the data type of what is returned.
> 1 "VALUE" vs 0...n "VALUES"
> Do you still fail to see the point 1 "VALUE" is special in the
> context of what a function returns?
>
>
> sorry, I don't understand .. for me SRF functions are absolutly
> different monsters than scalar, array or composite function - so its
> impossible to compare it.
When looking from the other end of the problem, we are
using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
when we really want scalars.

My understanding is that one main drivers of starting this thread
was wanting also guaranteed SCALAR versions of these.

And wanting them in a way that is easy to use.


Cheers


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

Re: [HACKERS] Better support of exported snapshots with pg_dump

[Michael Paquier]

On Wed, Sep 3, 2014 at 11:57 PM, Robert Haas  wrote:
> I didn't find that option to be terribly important then, but I don't
> see how we can possibly get by without it now, unless our goal is to
> make logical decoding as hard to use as we possibly can.

Yes. With 9.4 it is possible to take a consistent database snapshot
when creating a slot but it is tricky because of how ephemeral
exported snapshots are:
- When using CREATE_REPLICATION_SLOT, an exported snapshot lives only
for the time replication connection is done.
- pg_export_snapshot result only lives for the duration of the
transaction where function is called
- pg_create_logical_replication_slot cannot export a snapshot
So now (if I am correct), the only way to get a consistent dump from
database is to maintain open a replication connection after opening a
replication slot on it. Still it is really application-dependent,
assuming as well that schema is not modified as mentioned in this
thread. Any ways to facilitate the user experience on this side would
be a great step for things like online upgrades. Perhaps we could get
pg_dump or a wrapper on top of pg_dump creating a logical replication
slot, then taking a consistent image of the database it is based on
while replication connection is open.

> Tom's got a good point about the order of locking vs. snapshot taking,
> but I think the way to address that is by adding some capability to
> temporarily lock out all DDL on non-temporary objects across the
> entire system, rather than by trying to make pg_dump (or the walsender
> creating the replication slot) lock every table.  Even if we could get
> that to work, it still leaves the very-much-related problem that dumps
> of databases containing many tables can easily exhaust the lock table.

Yes this is an idea to dig. Having system-wide DDL locking is
something that has been discussed at some point in XC development for
the addition of new nodes (needed to ensure that schema was consistent
during migration of data) if I recall correctly. Now looking quickly
at the XC code git-grepping is showing a method based on
pg_try_advisory_lock_shared and a global boolean variable set in
PostgresMain, coupled with a check in ProcessUtility preventing a
certain category of DDL from running if a lock is taken. The good
point is that there is already some work done to detect what are the
utility statements that could be allowed even if lock is hold
(EXECUTE, VACUUM, CLUSTER, etc.).
Now, wouldn't a variable in shared memory controlled by some system
function a better option? There are as well some utility code paths
that we wouldn't want to block so we would end up with a switch on all
the DDL Stmt nodes or a large portion of them. Thoughts?
Regards,
-- 
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] Scaling shared buffer eviction

[Kevin Grittner]

Robert Haas  wrote:
> On Thu, Sep 4, 2014 at 7:25 AM, Amit Kapila  wrote:
>> Its not difficult to handle such cases, but it can have downside also
>> for the cases where demand from backends is not high.
>> Consider in above case if instead of 500 more allocations, it just
>> does 5 more allocations, then bgreclaimer will again have to go through
>> the list and move 5 buffers and same can happen again by the time
>> it moves 5 buffers.
>
> That's exactly the scenario in which we *want* the looping behavior.
> If that's happening, then it means it's taking us exactly as long to
> find 5 buffers as it takes the rest of the system to use 5 buffers.
> We need to run continuously to keep up.

That's what I was thinking, as long as there isn't a lot of
overhead to starting and finishing a cycle.  If there is, my
inclination would be to try to fix that rather than to sleep and
hope things don't get out of hand before it wakes up again.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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 1.2

[Marko Tiikkaja]

On 9/4/14 4:09 PM, Shaun Thomas wrote:

On 09/03/2014 04:19 PM, Hannu Krosing wrote:


1. Conditions for number of rows returned by SELECT or touched by
UPDATE or DELETE


Now that I think upon this... don't we already have it?

SELECT ... LIMIT 1


No, that just hides any bugs.  We want the opposite: any bugs or 
problems should be obvious.  If the query returns or touches more than 
one row, that should raise an error, not just give you a random one and 
call it a day.



That already solves the purported problem of multiple results in SELECT
INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too?


Again, this is a different problem, but LIMIT syntax for UPDATE and 
DELETE has been proposed, see: 
http://www.postgresql.org/message-id/CADB9FDf-Vh6RnKAMZ4Rrg_YP9p3THdPbji8qe4qkxRuiOwm=m...@mail.gmail.com



.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 1.2

[Shaun Thomas]

On 09/03/2014 04:19 PM, Hannu Krosing wrote:


1. Conditions for number of rows returned by SELECT or touched by
UPDATE or DELETE


Now that I think upon this... don't we already have it?

SELECT ... LIMIT 1

That already solves the purported problem of multiple results in SELECT 
INTO as well. Could we possibly extend that to UPDATE and DELETE syntax too?


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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] Escaping from blocked send() reprised.

[Robert Haas]

On Thu, Sep 4, 2014 at 9:53 AM, Heikki Linnakangas
 wrote:
> On 09/04/2014 04:37 PM, Robert Haas wrote:
>> Hrm.  So we'd have to block SIGUSR1, check the flag, then use
>> pselect() to temporarily unblock SIGUSR1 and wait, then on return
>> again unblock SIGUSR1?  Doesn't seem very appealing.  I think changing
>> the signal mask is fast on Linux, but quite slow on at least some
>> other UNIX-like platforms.  And I've heard that pselect() isn't always
>> truly atomic, so we might run into platform-specific bugs, too.  I
>> wonder if there's a better way e.g. using memory barriers.
>>
>> WaitLatch: check is_set.  if yes then done.  otherwise, set signal_me.
>> memory barrier.  recheck is_set.  if not set then wait using
>> poll/select. memory barrier.  clear signal_me.
>> SetLatch: check is_set.  if yes then done.  otherwise, set is_set.
>> memory barrier.  check signal_me.  if set, then send SIGUSR1.
>
> Doesn't work. No matter what you do, the process running WaitLatch might
> receive the signal immediately before it calls poll/select. The signal
> handler will run, and the poll/select call will then go to sleep. There is
> no way to do this without support from the kernel, that is why ppoll/pselect
> exist.

Eesh, I was confused there: ignore me.  I was trying to optimize away
the signal handling but assuming we still had the self-pipe byte.  But
of course in that case we don't need to change anything at all.

I'm going to go get some more caffeine.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[Robert Haas]

On Wed, Sep 3, 2014 at 12:35 AM, Noah Misch  wrote:
> On Tue, Sep 02, 2014 at 01:56:34AM -0400, Tom Lane wrote:
>> 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.
>
> I tried your patches against libedit-28.  Wherever a command contains a
> newline, unpatched psql writes the three bytes "\^A" to the history file, and
> patched psql writes the four bytes "\012".  Unpatched psql correctly reads
> either form of the history file.  Patched psql misinterprets a history file
> created by unpatched psql, placing 0x01 bytes in the recalled command where it
> should have newlines.  That's a worrisome compatibility break.

Worrisome seems like a strong word, but certainly irritating.  FWIW,
my Mac has psql linked to /usr/lib/libedit.3.dylib, is running 10.8.5,
and has history file lines that look like this:

select\0401\040union\040select\0401;

(You may wonder whether I actually get paid to craft such exciting SQL
commands.  Turns out I do.)

One point to note is that not back-patching this doesn't really fix
anything.  Will a user be annoyed when .psql_history fails to reload
properly on a new minor release, but utterly indifferent to whether it
reloads in a new major release?  What if they run multiple major
releases of PostgreSQL on the same machine, using the psql executable
for each version when talking to that version?  (Yeah, I know it's
backward compatible, but not everyone may realize that, or care.)

Given that, if we're going to do it this way at all, I favor
back-patching: at least then the newest releases of all supported
branches will be compatible with each other.  But I'm still fuzzy on
why we need to give up the ability to read the old format in the first
place.  Can't we just fix that and be done with this?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Escaping from blocked send() reprised.

[Heikki Linnakangas]

On 09/04/2014 04:37 PM, Robert Haas wrote:

Hrm.  So we'd have to block SIGUSR1, check the flag, then use
pselect() to temporarily unblock SIGUSR1 and wait, then on return
again unblock SIGUSR1?  Doesn't seem very appealing.  I think changing
the signal mask is fast on Linux, but quite slow on at least some
other UNIX-like platforms.  And I've heard that pselect() isn't always
truly atomic, so we might run into platform-specific bugs, too.  I
wonder if there's a better way e.g. using memory barriers.

WaitLatch: check is_set.  if yes then done.  otherwise, set signal_me.
memory barrier.  recheck is_set.  if not set then wait using
poll/select. memory barrier.  clear signal_me.
SetLatch: check is_set.  if yes then done.  otherwise, set is_set.
memory barrier.  check signal_me.  if set, then send SIGUSR1.


Doesn't work. No matter what you do, the process running WaitLatch might 
receive the signal immediately before it calls poll/select. The signal 
handler will run, and the poll/select call will then go to sleep. There 
is no way to do this without support from the kernel, that is why 
ppoll/pselect exist.


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

[Michael Paquier]

On Thu, Sep 4, 2014 at 1:44 PM, Fujii Masao  wrote:
> On Thu, Aug 28, 2014 at 8:46 PM, Fujii Masao  wrote:
>> Good catch. So I will remove start_xact code later.
> Attached patch removes start_xact from PSQLexec.
Nothing negative to say here :)
Patch simply removes the second argument of PSQLexec that was set to
the same value everywhere, aka false as noticed by Heikki. Comments
and code blocks related to this parameter are removed, and the code
compiles, passing check-world as well (just kicked the tests in case).
Regards,
-- 
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] PL/pgSQL 1.2

[Pavel Stehule]

2014-09-04 15:38 GMT+02:00 Jan Wieck :

> On 09/04/2014 09:31 AM, Pavel Stehule wrote:
>
>> 2014-09-04 15:24 GMT+02:00 Jan Wieck >
>> I think I like the COMMAND CONSTRAINT the best so far.
>>
>>
>> I not, because when it will not be part of SQL, than parser in plpgsql
>> will be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE
>>
>
> Making the COMMAND CONSTRAINT part of the core SQL parser was how I
> understood Hannu's idea. It would be horrible to tuck that feature away
> inside of a PL, rather than making it available to all PLs as well as
> applications, that use SQL directly (I think there still are two or three
> applications that do).


So I am happy so we have agreement, so implementation on PL level can be
terrible.

Pavel



>
>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

Re: [HACKERS] PL/pgSQL 1.2

[Jan Wieck]

On 09/04/2014 09:31 AM, Pavel Stehule wrote:

2014-09-04 15:24 GMT+02:00 Jan Wieck 

Making the COMMAND CONSTRAINT part of the core SQL parser was how I 
understood Hannu's idea. It would be horrible to tuck that feature away 
inside of a PL, rather than making it available to all PLs as well as 
applications, that use SQL directly (I think there still are two or 
three applications that do).



Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


--
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] Escaping from blocked send() reprised.

[Robert Haas]

On Thu, Sep 4, 2014 at 9:05 AM, Heikki Linnakangas
 wrote:
> Hmm. Perhaps we should call drainSelfPipe() only after poll/select returns
> saying that there is something in the self-pipe. That would be a win
> assuming it's more common for the self-pipe to be empty.

Couldn't hurt.

>> But my
>> impression was that those platforms were somewhat obscure.  Could we
>> have a separate latch implementation for platforms where we know that
>> system calls will get interrupted by signals?
>
> ... and have ppoll or pselect. Yeah, seems reasonable, assuming that
> ppoll/pselect is faster.

Hrm.  So we'd have to block SIGUSR1, check the flag, then use
pselect() to temporarily unblock SIGUSR1 and wait, then on return
again unblock SIGUSR1?  Doesn't seem very appealing.  I think changing
the signal mask is fast on Linux, but quite slow on at least some
other UNIX-like platforms.  And I've heard that pselect() isn't always
truly atomic, so we might run into platform-specific bugs, too.  I
wonder if there's a better way e.g. using memory barriers.

WaitLatch: check is_set.  if yes then done.  otherwise, set signal_me.
memory barrier.  recheck is_set.  if not set then wait using
poll/select. memory barrier.  clear signal_me.
SetLatch: check is_set.  if yes then done.  otherwise, set is_set.
memory barrier.  check signal_me.  if set, then send SIGUSR1.

>> Alternatively, should
>> we consider reimplementing latches using semaphores?  I assume having
>> the signal handler up the semaphore would allow the attempt to down
>> the semaphore to succeed on return from the handler, so it would
>> accomplish the same thing as the self-pipe trick.
>
> I don't think there's a function to wait for a file descriptor or semaphore
> at the same time.

Oh, good point.  So that's out, then.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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 1.2

[Pavel Stehule]

2014-09-04 15:24 GMT+02:00 Jan Wieck :

> On 09/04/2014 01:14 AM, Pavel Stehule wrote:
>
>> 2014-09-03 23:19 GMT+02:00 Hannu Krosing > A more SQL-ish way of doing the same could probably be called COMMAND
>> CONSTRAINTS
>> and look something like this
>>
>> SELECT
>> ...
>> CHECK (ROWCOUNT BETWEEN 0 AND 1);
>>
>>
>> It is very near to my proposed ASSERT
>>
>
> Only if the ASSERT syntax would become part of the original statement, it
> is supposed to check. In Hannu's command constraint example above, the
> statement that causes the error, and thus will be logged and become
> identified by the error message, is the actual SELECT (or other DML
> statement).
>

this is valid argument.

On second hand, I proposed a ASSERT that was not based on expressions only.
There is not a technical issue to write assert with knowledge of related
statement.


>
> I think I like the COMMAND CONSTRAINT the best so far.
>

I not, because when it will not be part of SQL, than parser in plpgsql will
be more complex. You have to inject SELECT, UPDATE, INSERT, DELETE

Pavel


>
>
> Regards,
> Jan
>
> --
> Jan Wieck
> Senior Software Engineer
> http://slony.info
>

Re: [HACKERS] missing tab-completion for relation options

[Michael Paquier]

On Thu, Sep 4, 2014 at 1:53 PM, Fujii Masao  wrote:
> Attached patch adds the missing tab-completion for the relation
> options like autovacuum_multixact_freeze_max_age.

That's a nice catch. Multixact parameters are present since 9.3.
user_catalog_table since 9.4.
Regards,
-- 
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] PL/pgSQL 1.2

[Jan Wieck]

On 09/04/2014 01:14 AM, Pavel Stehule wrote:

2014-09-03 23:19 GMT+02:00 Hannu Krosing 

Only if the ASSERT syntax would become part of the original statement, 
it is supposed to check. In Hannu's command constraint example above, 
the statement that causes the error, and thus will be logged and become 
identified by the error message, is the actual SELECT (or other DML 
statement).


I think I like the COMMAND CONSTRAINT the best so far.


Regards,
Jan

--
Jan Wieck
Senior Software Engineer
http://slony.info


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

[Shaun Thomas]

On 09/01/2014 04:04 AM, Joel Jacobson wrote:


+ Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
row, as that's the most common use-case, and provide alternative syntax
to modify multiple or zero rows.


What? No. The whole point of SQL is that it's set-based and can modify 
multiple rows at once. Hobbling it specifically for functions seems 
fundamentally flawed. Especially for what we purport to be a core 
PostgreSQL language.



+ Change all warnings into errors


I... what? I could see coming up with a better exception handling 
mechanism for escalating messages. But you're talking about taking a 
core element of PostgreSQL (warnings) and simply ripping them out so 
plpgsql2 loses even that small functionality.


I'm sure you've put a lot of thought into this, but you're not the only 
person using plpgsql or any, however ambitious, potential replacement.


--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
stho...@optionshouse.com

__

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to 
this email


--
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: [HACKERS] RE: 答复: [HACKERS] why after increase the hash table partitions, TPMC decrease

[Robert Haas]

On Tue, Sep 2, 2014 at 11:02 PM, Xiaoyulei  wrote:
> benchmarSQL has about half reads. So I think it should be effective.
>
> I don't think BufFreelistLock take much time, it just get a buffer from list. 
> It should be very fast.

You're wrong.  That list is usually empty right now; so it does a
linear scan of the buffer pool looking for a good eviction candidate.

> The test server has 2 CPUs and 12 cores in each CPU. 24 processor totally. 
> CPU Idle time is over 50%. IO only 10%(data is in SSD)
>
> I perf one process of pg. The hot spot is hash search. Attachment is perf 
> data file.

I think you need to pass -g to perf so that you get a call-graph
profile.  Then you should be able to expand the entry for
hash_search_with_hash_value() and see what's calling it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
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] Escaping from blocked send() reprised.

[Heikki Linnakangas]

On 09/04/2014 03:49 PM, Robert Haas wrote:

On Tue, Sep 2, 2014 at 3:01 PM, Andres Freund  wrote:

I'm slightly worried about the added overhead due to the latch code. In
my implementation I only use latches after a nonblocking read, but
still. Every WaitLatchOrSocket() does a drainSelfPipe(). I wonder if
that can be made problematic.


I think that's not the word you're looking for.  Or if it is, then -
it's already problematic.  At some point I hacked up a very crude
prototype that made LWLocks use latches to sleep instead of
semaphores.  It was slow.


Hmm. Perhaps we should call drainSelfPipe() only after poll/select 
returns saying that there is something in the self-pipe. That would be a 
win assuming it's more common for the self-pipe to be empty.



AIUI, the only reason why we need the self-pipe thing is because on
some platforms signals don't interrupt system calls.


That's not the only reason. It also eliminates the race condition that 
someone might set the latch after we've checked that it's not set, but 
before calling poll/select. The same reason that ppoll and pselect exist.



But my
impression was that those platforms were somewhat obscure.  Could we
have a separate latch implementation for platforms where we know that
system calls will get interrupted by signals?


... and have ppoll or pselect. Yeah, seems reasonable, assuming that 
ppoll/pselect is faster.



Alternatively, should
we consider reimplementing latches using semaphores?  I assume having
the signal handler up the semaphore would allow the attempt to down
the semaphore to succeed on return from the handler, so it would
accomplish the same thing as the self-pipe trick.


I don't think there's a function to wait for a file descriptor or 
semaphore at the same time.


- Heikki


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