bitfield and gcc

I wonder if somewhere in Postgres source "we" are relying on the GCC 
"correct behaviour" regarding the read-modify-write of bitfield in
structures.

Take a read at this https://lwn.net/Articles/478657/

sorry if this was already mentioned.

Regards
Gaetano Mendola

--

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

auto_explain produces invalid JSON

The auto_explain module appears to create invalid JSON (in all versions
since 9.0).  For example, with the settings

auto_explain.log_format = 'json'
auto_explain.log_min_duration = 0

the query

select * from pg_type;

produces this in the log:

LOG:  duration: 529.808 ms  plan:
        [
          "Query Text": "select * from pg_type;",
          "Plan": {
            "Node Type": "Seq Scan",
            "Relation Name": "pg_type",
            "Alias": "pg_type",
            "Startup Cost": 0.00,
            "Total Cost": 9.87,
            "Plan Rows": 287,
            "Plan Width": 611
          }
        ]

Note that at the top level, it uses the array delimiters [ ] for what is
actually an object (key/value).  Running this through a JSON parser will
fail.

Fix PL/Python metadata when there is no result

Dear hackers,

Thanks for the work on PLPython result metadata, it is very useful!  I
just came across a crash when trying to access this metadata on the
result of an UPDATE, which obviously cannot return any tuple (unless
you specify a RETURNING clause maybe?).

Please find attached a patch that solves this issue.  Instead of a PG
crash, we get the following message:

ERROR:  plpy.Error: no result fetched

All the best,
-- 
Jean-Baptiste Quenot

--

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

Upcoming PG back-branch releases, end of this month

In view of the recently fixed data-corruption issues in hot standby
operation (bugs 6200, 6425), the core team feels we should push out back
branch update releases soon.  Due to unavailability of some key people,
the earliest feasible schedule turns out to be wrap Thursday 2/23 for
public release Monday Feb 27.  So that's what it will be.

			regards, tom lane

--

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

Patch: fix pg_dump for inherited defaults & not-null flags

Attached is a proposed patch to deal with the issue described here:
http://archives.postgresql.org/pgsql-bugs/2012-02/msg00000.php

Even though we'd previously realized that comparing the text of
inherited CHECK expressions is an entirely unsafe way to detect
expression equivalence (cf comments for guessConstraintInheritance),
pg_dump is still doing that for inherited DEFAULT expressions, with
the predictable result that it does the wrong thing in this sort
of example.

Furthermore, as I looked more closely at the code, I realized that
there is another pretty fundamental issue: if an inherited column has a
default expression or NOT NULL bit that it did not inherit from its
parent, flagInhAttrs forces the column to be treated as non-inherited,
so that it will be emitted as part of the child table's CREATE TABLE
command.  This is *wrong* if the column is not attislocal; it will
result in the column incorrectly having the attislocal property after
restore.  (Note: such a situation could only arise if the user had
altered the column's default or NOT NULL property with ALTER TABLE after
creation.)

All of this logic predates the invention of attislocal, and really is
attempting to make up for the lack of that bit, so it's not all that
surprising that it falls down.

So the attached patch makes the emit-column-or-not behavior depend only
on attislocal (except for binary upgrade which has its own kluge
solution for the problem; though note that the whether-to-dump tests now
exactly match the special cases for binary upgrade, which they did not
before).  Also, I've dropped the former attempts to exploit inheritance

RFC: Making TRUNCATE more "MVCC-safe"

Hi!

I've always been a little wary of using the TRUNCATE command due to
the warning in the documentation about it not being "MVCC-safe":
queries may silently give wrong results and it's hard to tell when
they are affected.

That got me thinking, why can't we handle this like a standby server
does -- if some query has data removed from underneath it, it aborts
with a serialization failure.

Does this solution sound like a good idea?

The attached patch is a lame attempt at implementing this. I added a
new pg_class.relvalidxmin attribute which tracks the Xid of the last
TRUNCATE (maybe it should be called reltruncatexid?). Whenever
starting a relation scan with a snapshot older than relvalidxmin, an
error is thrown. This seems to work out well since TRUNCATE updates
pg_class anyway, and anyone who sees the new relfilenode automatically
knows when it was truncated.

Am I on the right track? Are there any better ways to attach this
information to a relation?
Should I also add another counter to pg_stat_database_conflicts?
Currently this table is only used on standby servers.

Since I wrote it just this afternoon, there are a few things still
wrong with the patch (it doesn't handle xid wraparound for one), so
don't be too picky about the code yet. :)

psql tab completion for SELECT

In his blog entry http://www.depesz.com/2011/07/08/wish-list-for-psql/
depesz described a simple way to do tab completion for SELECT in psql:

"""
Make tab-completion complete also function names – like: SELECT
pg_get<tab><tab> to see all functions that start with pg_get.

Make tab-completion work for columns in SELECT. I know that when writing
SELECT clause, psql doesn’t know which table it will deal with, but it
could search through all the columns in database.
"""

That seems pretty useful, and it's more or less a one-line change, as in
the attached patch.

diff --git i/src/bin/psql/tab-complete.c w/src/bin/psql/tab-complete.c
index 3854f7f..416aa2f 100644
--- i/src/bin/psql/tab-complete.c
+++ w/src/bin/psql/tab-complete.c
@@ -2555,7 +2555,9 @@ psql_completion(char *text, int start, int end)
 		COMPLETE_WITH_CONST("IS");

 /* SELECT */
-	/* naah . . . */
+	/* complete with columns and functions */
+	else if (pg_strcasecmp(prev_wd, "SELECT") == 0)
+		COMPLETE_WITH_QUERY("SELECT name FROM (SELECT attname FROM pg_attribute UNION SELECT proname ||
'(' FROM pg_proc) t (name) WHERE substring(name,1,%d)='%s'");

index-only quals vs. security_barrier views

When Heikki worked up his original index-only scan patches (which
didn't end up looking much like what eventually got committed), he had
the notion of an index-only qual.  That is, given a query like this:

select sum(1) from foo where substring(a,1,3) = 'abc';

We could evaluate the substring qual before performing the heap fetch,
and fetch the tuple from the heap only if the qual passes.  The
current code is capable of generating an index-only scan plan for this
query, but the MVCC visibility check always happens first: if the page
is all-visible, we go ahead and evaluate the qual using only the index
data, but if the page is not all-visible, we do the heap fetch first
and then check the qual only if the tuple is visible to our snapshot.
It would be nice to have the ability to do those checks in the other
order, in the case where the projected cost of checking the qual is
less than the projected cost of the heap fetch.  This would allow
index-only scans to win in more situations than they can right now,
because we'd conceivably avoid quite a bit of random I/O if the qual
is fairly selective and there are a decent number of visibility map
bits that are unset.

In fact, this technique might pay off even if we don't have a covering index:

select * from foo where substring(a,1,3) = 'abc';

If the expected selectivity of the qual is low and the index is a lot
smaller than the table, we might want to iterate through all the index
tuples in the entire index and fetch the heap tuples for only those
where the qual passes.  This would allow index-only scans - or
whatever term you want to use, since there's not much that's index

Notify system doesn't recover from "No space" error

On a development system which wasn't being monitored very closely
I think we've uncovered a bug in the listen/notify behavior.  The
system was struggling with lack of disk space for about a week
before this surfaced, so it's probably not the most critical sort
of bug we could have, but it probably merits a fix.

We were getting all sorts of logging about the space problem, and
PostgreSQL soldiered bravely on, doing the best it could under such
adverse conditions.  (That's pretty impressive, really.)  A lot of
messages like these:

[2012-02-05 01:24:36.816 CST] 14950 <cc cc 127.0.0.1(35321)> ERROR: 
could not extend file "base/16401/46507734": wrote only 4096 of 8192
bytes at block 0
[2012-02-05 01:24:36.816 CST] 14950 <cc cc 127.0.0.1(35321)> HINT: 
Check free disk space.

[2012-02-05 01:25:40.643 CST] 5323 LOG:  could not write temporary
statistics file "pg_stat_tmp/pgstat.tmp": No space left on device

[2012-02-05 01:26:38.971 CST] 15065 <cc cc 127.0.0.1(53258)> ERROR: 
could not write to hash-join temporary file: No space left on device

I don't know how much of a clue this is, but less than a minute
before our first message about pg_notify problems, the statistics
messages went from complaining about not being able to *write* the
file to also complaining about not being able to *close* the file --
like this:

[2012-02-05 01:26:41.159 CST] 5323 LOG:  could not close temporary

pl/perl and utf-8 in sql_ascii databases

Hi,

we have a database that is storing strings in various encodings (and
non-encodings, namely the arbitrary byte soup that you might see in
email headers from the internet). For this reason, the database uses
sql_ascii encoding. The columns are text, as most characters are
ascii, so bytea didn't seem the right way to go.

Currently we are on 8.3 and try to upgrade to 9.1, but the plperlu
functions we have are acting up.

Old behavior on 8.3 .. 9.0:

sql_ascii =# create or replace function whitespace(text) returns text
language plperlu as $$ $a = shift; $a =~ s/[\t ]+/ /g; return $a; $$;
CREATE FUNCTION

sql_ascii =# select whitespace (E'\200'); -- 0x80 is not valid utf-8
 whitespace 
------------

sql_ascii =# select whitespace (E'\200')::bytea;
 whitespace 
------------
 \x80

New behavior on 9.1.2:

sql_ascii =# select whitespace (E'\200');
ERROR:  XX000: Malformed UTF-8 character (fatal) at line 1.

pg_receivexlog and sync rep Re: Updated version of pg_receivexlog

On Tue, Oct 25, 2011 at 7:37 PM, Magnus Hagander <magnus <at> hagander.net> wrote:
> On Mon, Oct 24, 2011 at 14:40, Magnus Hagander <magnus <at> hagander.net> wrote:
>> On Mon, Oct 24, 2011 at 13:46, Heikki Linnakangas
>> <heikki.linnakangas <at> enterprisedb.com> wrote:
>>> How does this interact with synchronous replication? If a base backup that
>>> streams WAL is in progress, and you have synchronous_standby_names set to
>>> '*', I believe the in-progress backup will count as a standby for that
>>> purpose. That might give a false sense of security.
>>
>> Ah yes. Did not think of that. Yes, it will have this problem.
>
> Actually, thinking more, per other mail, it won't. Because it will
> never report that the data is synced to disk, so it will not be
> considered for sync standby.

Now, new replication mode (synchronous_commit = write) is supported.
In this mode, the in-progress backup will be considered as sync
standby because its periodic status report includes the valid write position.
We should change the report so that it includes only invalid positions.
Patch attached.

While I agree that the backup should not behave as sync standby, ISTM
that pg_receivexlog should, which is very useful. If pg_receivexlog does
so, we can write WAL synchronously in both local and remote, which
would increase the durability of the system. Thus, to allow pg_receivexlog
to behave as sync standby, we should change it so that its status report
includes the write and flush positions?

Regards,