date:20131111

On 11/12/2013 02:35 PM, Tom Lane wrote:

>> [ lots o details snipped ]
>> Totally crazy? Or workable? I'm extremely new to the planner, so I know
>> this might be unworkable, and would value advice.
> 
> The main omission I notice in your sketch is that the join tree that is
> the source of tuples has to produce both the ctid of the row to be
> updated, and values for *all* the columns of the target relation.
> So for any column that's not updated explicitly in the UPDATE command,
> we have to fetch the old value.  IOW, if we have a table with columns
> x,y,z, and the original command is
> 
>UPDATE ... SET y = something FROM ...
> 
> then we effectively transform that to
> 
>UPDATE ... SET x = x, y = something, z = z FROM ...
> 
> and so we have to pull old values of x and z, as well as whatever
> we need to calculate the "something".

That makes sense. I was just reading the relevant part of
expand_targetlist when I got your mail. I don't think it makes much
difference.

Thankyou very much for taking the time to read this proposal. The fact
that it doesn't look immediately unworkable to you makes me hopeful. I'm
going to start prototyping it now.

> What I've not seen explained here is what is different between updating a
> security barrier view and the already-implemented logic for updating
> a plain view?

The current updatable view logic pulls up the view quals out of the view
subquery then flattens the view subquery. It's basically duplicating
logic done in the optimizer and notes that in the comments.

We can't do this for security barrier views, that's the whole point of
them. If we pull up quals we re-introduce the leaky view problem for
updates, eg:

UPDATE some_sb_view SET col = 'val' WHERE f_leak(secretcol);

If support for updating a subquery is added we might be able to get rid
of the pull-up code in the current updatable view support. Just let the
update pass through into the planner as an update over a subquery, and
then let the planner flatten it if it isn't a security barrier, just
like it does for a SELECT.

-- 
 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] Updatable security_barrier views (was: [v9.4] row level security)

Craig Ringer  writes:
> On 11/12/2013 05:40 AM, Robert Haas wrote:
>> I haven't studied this issue well enough to know what's really needed
>> here, but Dean Rasheed's approach sounded like a promising tack to me.

> I've been looking further into adding update support for security
> barrier views and after reading the code for UPDATE ... FROM I don't
> understand why there was any need to add separate targetRelation and
> sourceRelation plan attributes.

I've not read the prior patch, but that sounds a bit bogus to me too.

> [ lots o details snipped ]
> Totally crazy? Or workable? I'm extremely new to the planner, so I know
> this might be unworkable, and would value advice.

The main omission I notice in your sketch is that the join tree that is
the source of tuples has to produce both the ctid of the row to be
updated, and values for *all* the columns of the target relation.
So for any column that's not updated explicitly in the UPDATE command,
we have to fetch the old value.  IOW, if we have a table with columns
x,y,z, and the original command is

   UPDATE ... SET y = something FROM ...

then we effectively transform that to

   UPDATE ... SET x = x, y = something, z = z FROM ...

and so we have to pull old values of x and z, as well as whatever
we need to calculate the "something".  I don't think this invalidates
anything you said, but it did seem to be missing from the sketch
(in particular, we need those old values independently of whether
there's any RETURNING clause, because they have to be stored back
into the freshly-formed updated tuple).

What I've not seen explained here is what is different between updating a
security barrier view and the already-implemented logic for updating
a plain view?

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] Fwd: Test of Algorithm || Indexing Scheme

2013-11-11 Thread Atri Sharma

On Tue, Nov 12, 2013 at 11:53 AM, Craig Ringer  wrote:
> On 11/12/2013 03:26 AM, Rohit Goyal wrote:
>> Hi,
>>
>> Actually,
>>
>> I want to test an algorithm in which I will use store tuple_id in some
>> other data structure and value of the key of index will contain some
>> random created by me. Could you please tel me which file need to be
>> change for it.
>>
>> you said something about usage of GiST opclass. Can you please elaborate
>> ur opinion in detail. It would be of a great help to me.
>
> I think you really need to take a few steps back and explain *why* you
> feel you want to modify the b-tree values.

+1

>
> Are you attempting to implement a covering index or index-organized table?
>
> "some random created by me" tells us around about nothing about what
> you're trying to do.




-- 
Regards,

Atri
l'apprenant


-- 
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] Fwd: Test of Algorithm || Indexing Scheme

On 11/12/2013 03:26 AM, Rohit Goyal wrote:
> Hi, 
> 
> Actually, 
> 
> I want to test an algorithm in which I will use store tuple_id in some
> other data structure and value of the key of index will contain some
> random created by me. Could you please tel me which file need to be
> change for it.
> 
> you said something about usage of GiST opclass. Can you please elaborate
> ur opinion in detail. It would be of a great help to me.

I think you really need to take a few steps back and explain *why* you
feel you want to modify the b-tree values.

Are you attempting to implement a covering index or index-organized table?

"some random created by me" tells us around about nothing about what
you're trying to do.


-- 
 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] pg_dump and pg_dumpall in real life

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/11/2013 09:59 PM, Rafael Martinez wrote:
> * We need a pg_dump solution that can generate in one step all the 
> necessary pieces of information needed when restoring or cloning a 
> database. (schema, data, privileges, users and alter database/role
> data)

... and if some users/roles already exist, but have different
meanings? Or some roles exist and some don't?

I'm very strongly against adding and using CREATE ROLE IF NOT EXISTS.
pg_restore should handle this case-by-case, forcing the user to
specify explicitly role-by-role that they want a given role in the
existing DB re-used if it exists, or want a new one created with a new
name in case of a clash.

A --rename-all-conflicting-roles and --reuse-all-conflicting-roles
option could be added for the all-or-none options. IMO if neither is
specified, the existence of any role name conflict should be a restore
error.

> * It would be great to be able to tell pg_restore that user1 in
> the dump will became user2 in the restored/cloned database. The
> same for the name of the database.

Agreed that this would be useful. Needs to deal with the case where
the users should be separated but they should remain a member of some
common role, though - eg "olduser" becomes "newuser" but the dumped
"olduser" was member of role "users" and "newuser" should also be
member of "users", not some renamed role.

- -- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.15 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQEcBAEBAgAGBQJSgcfKAAoJELBXNkqjr+S2XWIH/2c1Mcd4ldVTAPw/jAln4gNM
YH8SRPlsGU0fqfbYoKg/1y0K/Wdjdlac9bjjGzYpODryaXGopf1i+pWaphF2kJTM
LeMRVgEFEW7u2Dr6FXajQTQCiXLnA8C16NmmgIdqZZgYCsOwCorG+gFNfI8fZyft
okCQpYcljGXzlc218DI6/o4OZBBSdLh8diTzF8+xywoXJZopdAwfHDPPpAvizPye
rcUUkq1svArq78HakSuI8HoCy3ZHuiCf8mQEUPcLhFrwgh+bkrs29W7YAdD75gr4
yp32XeyOY5npXHaG9mHghs7anbUnwywJVEzpwKAf0SyPe7zunw8fdtx2NSF70no=
=dusS
-END PGP SIGNATURE-

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

[HACKERS] Updatable security_barrier views (was: [v9.4] row level security)

On 11/12/2013 05:40 AM, Robert Haas wrote:
> I haven't studied this issue well enough to know what's really needed
> here, but Dean Rasheed's approach sounded like a promising tack to me.

I've been looking further into adding update support for security
barrier views and after reading the code for UPDATE ... FROM I don't
understand why there was any need to add separate targetRelation and
sourceRelation plan attributes.

UPDATE ... FROM already operates on the target relation specified in the
:resultRelation of the QUERY node; it's a index into the range-table.

It's already quite happy getting its input rows from a tree of joins and
other plan nodes; all it seems to need is the ctid, any old values of
columns to be emitted via RETURNING, and the expressions for any new
column values in the top-level query.

It looks like

  UPDATE t SET t.somecol = t2.othercol
  FROM t2
  WHERE t.id = t2.id;

is already handled as if it were the (imaginary, but hopefully self
explanatory) sql:

UPDATE (
  SELECT t.ctid AS t_ctid, t2.othercol AS othercol
  FROM t
  INNER JOIN t2 ON t.id = t2.id
) sq
TARGET t
SET t.othercol = sq.othercol
WHERE t.ctid = sq.ctid;

where the :resultRelation identifies the RTI of the rel that the new
tuples should be added to and that old tuples should have their xmax set in.

If my understanding is vaguely right, adding sourceRelation and
targetRelation should not be necessary. We just have to transform the
query tree at the appropriate stage, so that a query over a security
barrier view emits a plan like this:

- resultRelation points to the RTE of the base relation discovered
  by recursively scanning S.B. subqueries and added to the RangeTable.
  This is the underlying RTE_RELATION. It should already be in the
  RangeTable but might need to be copied to appear with different
  required permissions for use in the ModifyTable node.

- S.B. subqueries in the plan are rewritten to add the ctid column
  as resjunk. This is much the same as what happens when a MergeJoin,
  HashJoin, or NestLoop node is under the ModifyTable - they're
  adjusted to add the ctid column by expand_targetlist in
  backend/optimizer/prep/preptlist.c . This is analogous to the
  current attr/var fixups in RLS and Dean's patch, but it should
  hopefully be possible to do it more cleanly in expand_targetlist.
  It's the biggest question mark so far.

- The test for subquery as result relation in preprocess_targetlist
  (backend/optimizer/prep/preptlist.c)
  remains in place. The subquery isn't the result relation, the
  underlying RTE_RELATION the stack of subqueries are based on is.

- When the updatable view code sees a s.b. view, expand it like a normal
  view but don't pull up the quals and flatten out the subquery. May
  need to update :resultRelation.

The executor will pull rows from the plan - running nested subquery
nodes, etc - and will get result tuples containing the ctid of the tuple
in the base rel, the old values of the fields in the row if needed for
RETURNING, and any computed cols for new values.

That will feed into the ModifyTable node, which will use the
:resultRelation just like it did before, without caring in the slightest
that the inputs came from a subquery.

I'm going to try to implement it as an experiment, see if I can make it
work. If I'm totally barking up the wrong tree or have missed something
major, please feel free to say so.

"simply updatable" views already include those that contain a subquery
over another table, just not directly in FROM, eg:

regress=> CREATE VIEW t_even_sq AS SELECT t.* FROM t
  WHERE EXISTS (SELECT 1 FROM t2 WHERE t.id = t2.id);
CREATE VIEW
regress=> UPDATE t_even_sq SET id = id;
UPDATE 10

so this won't stop row-security from using subqueries to refer to other
relations in access tests.

Determining which rel to update in the presence of subqueries over other
tables should just involve making sure the right :resultRelation is kept
track of during recursive view expansion.

Totally crazy? Or workable? I'm extremely new to the planner, so I know
this might be unworkable, and would value advice.
-- 
 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] ECPG FETCH readahead

2013-11-11 Thread Noah Misch

On Mon, Nov 11, 2013 at 10:17:54AM +0100, Boszormenyi Zoltan wrote:
> The old contents of my GIT repository was removed so you need to
> clone it fresh. https://github.com/zboszor/ecpg-readahead.git
> I won't post the humongous patch again, since sending a 90KB
> compressed file to everyone on the list is rude.

Patches of that weight show up on a regular basis.  I don't think it's rude.

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.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] Re: Exempting superuser from row-security isn't enough. Run predicates as DEFINER?

On 11/11/2013 06:37 PM, Kohei KaiGai wrote:
> Hi Craig,
> 
> I'd like to vote the last options. It is a separate problem (or, might
> be specification), I think.

I tend to agree, but I'm nervous about entirely hand-waving around this,
as doing so would *expand* the existing problem.

"Solving" this properly would require adding a security context and
current user to subqueries, not just for permissions checks (as
currently exists) but for execution as well.

That's a whole separate job. So I'd say that for first stage RS we
should require that row-security policies only be defined by highly
privileged users (superuser, or user granted a new SETROWSECURITY
right). Table owners can't set row security on their own tables. That
way we don't expand the existing security issue that already exists by
making it easy to attack logical backups.

Between that and the ability to grant a right that exempts users from
row security (given to just superuser by default) we should be OK with
this problem.

Admins who choose to trust users not to write malicious RS predicates,
or who only run pg_dump as superuser and have isolated users, can choose
to grant their users the right to set their own row security policies.

-- 
 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] Heavily modified big table bloat even in auto vacuum is running

2013-11-11 Thread Amit Kapila

On Mon, Nov 11, 2013 at 3:14 PM, Haribabu kommi
 wrote:
> On 08 November 2013 18:35 Amit Kapila wrote:
>> On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
>>  wrote:
>> > On 07 November 2013 09:42 Amit Kapila wrote:
>> >> I am not sure whether the same calculation as done for
>> new_rel_tuples
>> >> works for new_dead_tuples, you can once check it.
>> >
>> > I didn't find any way to calculate new_dead_tuples like
>> new_rel_tuples.
>> > I will check it.
>> >
>> > The two approaches calculations are approximation values only.
>> >
>> > 1. Taking a copy of n_dead_tuples before VACUUM starts and then
>> subtract it once it is done.
>> >This approach doesn't include the tuples which are remains during
>> the vacuum operation.
>>
>>   Wouldn't next or future vacuum's will make the estimate more
>> appropraite?
>
> Possible only when nkeep counter value (tuples not cleaned) is very less 
> value.

   Do you really expect too many dead tuples during Vacuum?

>> > 2. nkeep counter contains the tuples which are still visible to other
>> transactions.
>> >This approach doesn't include tuples which are deleted on pages
>> where vacuum operation is already finished.
>> >
>> > In my opinion the second approach gives the value nearer to the
>> actual
>> > value, because it includes some of the new dead tuples also. Please
>> correct me if anything wrong in my analysis.
>>I think main problem in nkeep logic is to come up with an estimation
>> algorithm similar to live tuples.
>>
>> By the way, do you have test case or can you try to write a test case
>> which can show this problem and then after fix, you can verify if the
>> problem is resolved.
>
> The simulated index bloat problem can be generated using the attached script 
> and sql.
> With the fix of setting the dead tuples properly,

   Which fix here you are referring to, is it the one which you have
proposed with your initial mail?

> the bloat is reduced and by changing the vacuum cost
> Parameters the bloat is avoided.



With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.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_dump and pg_dumpall in real life

2013-11-11 Thread Fabrízio de Royes Mello

On Mon, Nov 11, 2013 at 8:20 PM, Josh Berkus  wrote:
>
>
> [...]
>
> Well, then we just need pg_restore to handle the "role already exists"
> error message gracefully.  That's all.  Or a "CREATE ROLE IF NOT EXISTS"
> statement, and use that for roles.
>

I'm working in a patch to add IF NOT EXISTS for all CREATE statements,
including of course the CREATE ROLE statement.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello

Re: [HACKERS] Clang 3.3 Analyzer Results

Kevin Grittner  writes:
> It does seem hard to believe that clang tools would find as enough
> problems that were missed by Coverity and Valgrind to account for
> all the warnings that are scrolling by; but it looks like it has
> pointed out at least *one* problem that's worth fixing.

Yeah, that's the thing --- quite a lot of people have looked at
Postgres with Coverity already.  If Clang is throwing up lots and
lots of warnings, the odds are *very* high that most of them are
false positives.  Running through such a list to see if there's
anything real isn't all that exciting a prospect.

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] pg_dump and pg_dumpall in real life

2013-11-11 Thread Andrew Dunstan



On 11/11/2013 05:50 PM, David Johnston wrote:

Andrew Dunstan wrote

A general ability to rename things would be good. In particular,
restoring schema x into schema y or table x into table y would be very
useful, especially if you need to be able to compare old with new.

compare old and new what?


Data is what I had in mind.

There have been plenty of times when I've been asked ex post to find out 
what's changed in some table in the last 24 hours or something like 
that, and all I've had to work with is yesterday's dump file. The 
handsprings you have to turn in order to get the old version of the 
table and the new version side by side make it painful - it would be 
nice to be able to say "restore this table but with that name," or 
"restore this table but into that schema".



cheers

andrew


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

Re: [HACKERS] Fast insertion indexes: why no developments

2013-11-11 Thread Jeff Janes

On Thu, Oct 31, 2013 at 12:43 AM, Leonardo Francalanci wrote:

> Jeff Janes wrote
> > True, but that is also true of indexes created in bulk.  It all has to
> > reach disk eventually--
> > [...]
> > If the checkpoint interval is as long as the partitioning period, then
> > hopefully the active index buffers get re-dirtied while protected in
> > shared_buffers, and only get written to disk once.
>
> Honestly, I made a lot of tests in the past, and I don't remember if I
> tried
> 15-minute checkpoints + high shared_buffers. That might work. I'm going to
> try it and see what happens.
>

You might want to go even beyond 15 minutes.

>
>
> Jeff Janes wrote
> > If the buffers get read, dirtied, and evicted from a small shared_buffers
> > over and over again
> > then you are almost guaranteed that will get written to disk multiple
> > times
>
> (as I understand, but I might be wrong):
> high shared_buffers don't help because in such a random index writing, lots
> and lots of pages get dirtied, even if the change in the page was minimal.
> So, in the "15-minute" period, you write the same pages over and over
> again.
>

But you write them only if you need to due to a checkpoint, needing new
buffers to read in something else that is not already in shared_buffers, or
because you are using a buffer-access-strategy that uses a ring.  If you
make checkpoints longs, it will cut down on the first.  If shared_buffers
is large enough to contain the active part of the indexes being updated,
that should cut down on the second.  I don't know if the third is a problem
or not--I think copy might try to use a ring-buffer, but I don't if it does
that for indexed table.

> Even if you have high shared_buffers, the same page will get sync-ed to
> disk
> multiple times (at every checkpoint).
>

If the active part of the indexes is much larger than you can could
possibly set shared_buffers to, then there is probably little point in
increasing shared_buffers from, say, 1% of the active index size to 8% of
it.  It only makes sense to increase it if you can do so large enough to
cover ~100% of the needed space.

> The idea of those "other" indexes is to avoid the random writing,
> maximizing
> the writing in sequence, even if that means writing more bytes. In other
> words: writing a full 8KB is no different than write 20 bytes in a page, as
> we'll have to sync the whole page anyway...
>

True, but that is the idea here as well.  If you can delay writing the page
until 20 bytes of it have been dirtied on 400 different occasions...

I'm not saying we shouldn't think about some kind of insert buffer, but I
really doubt that that is going to happen in 9.4 while increasing
shared_buffers can be done today, if it works and if you can live with the
consequences.

Cheers,

Jeff

Re: [HACKERS] Clang 3.3 Analyzer Results

On Mon, Nov 11, 2013 at 6:01 PM, Kevin Grittner  wrote:
> Peter Geoghegan  wrote:
>> Kevin Grittner  wrote:
>>
>>> I'm currently capturing a text version of all the warnings from
>>> this.  Will gzip and post when it finishes.  It's generating a lot
>>> of warnings; I have no idea how many are PostgreSQL problems and
>>> how many are false positives; will just post the whole set FWIW.  I
>>> am using the 3.4 development nightly snapshot with these commands:
>>
>> When I tried out scan-build a while ago, the results were kind of
>> disappointing - there were lots of false positives. Clearly the tool
>> was inferior to Coverity at that time. I'd be interested to see if
>> there has been much improvement since.
>
> Perhaps it will be of some value in terms of filing additional bug
> reports with clang if it proves to have so many false positives
> that it has little value in evaluating PostgreSQL code.
>
> It does seem hard to believe that clang tools would find as enough
> problems that were missed by Coverity and Valgrind to account for
> all the warnings that are scrolling by; but it looks like it has
> pointed out at least *one* problem that's worth fixing.
>
> Ah, it finished.  Results attached; I haven't had time to review
> them yet.
The sanitizers are *bad ass*, especially the undefined behavior
checker (UBC or UBSan). There are no false positives when using it.

UBSan was based upon Peng and Regher's Integer Overflow Checker (we
used IOC before Clang 3.3 because Clang prior did not have checkers).
See http://embed.cs.utah.edu/ioc/ for details.

I've used the checkers to find a number of issues in libraries during
acceptance/integration testing, including Botan, Crypto++, libevent,
libnetcpp, OpenSSL and Squid. Some libraries were so bad it was more
like "Rejection Testing" (those have not been named).

UBSan is the tool of choice when your stuff does not work after
compiling with Intel's ICC. ICC generates the fastest code I have
seen, and it is ruthless about dropping undefined behavior in an
effort to speed up execution.

By the way, that -fwrapv is a crutch for illegal programs. It would be
wise to fix the problems rather than masking them with -fwrapv. You
can use UBSan to help ferret out the problems that -fwrapv hides. See
Ian Lance Taylor's blog for details:
http://www.airs.com/blog/archives/120.

Jeff

-- 
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] Fast insertion indexes: why no developments

2013-11-11 Thread Jeff Janes

On Tue, Nov 5, 2013 at 9:52 AM, Leonardo Francalanci wrote:

> Jeff Janes wrote
> > Some experiments I did a few years ago showed that applying sorts to the
> > data to be inserted could be helpful even when the sort batch size was as
> > small as one tuple per 5 pages of existing index.  Maybe even less.
>
> Cool!!! Do you have any idea/hint on how I could try and replicate that?
> Do you remember how you did it?
>

I can't find my notes but I remember more or less how I did it.

Since we don't yet have an insertion buffer that allows the rows to be
sorted in different order for different indexes, I had to simulate it just
by using a table with a single index and hoping that that would extrapolate.

create table foo (x bigint);

To speed things up, you may want to prepopulate this with random data so
that the size of the index-to-be will exceed shared_buffers, or physical
RAM, before making the index.  Also, the effectiveness might depend on how
much the index has grown since its creations, since leaf pages are
initially correlated between physical order and logical order, but that
decreases over time.  So you may want to try different initial seed sizes.

create index on foo (x);

Then I use perl to make run-sorted data with different run sizes, and load
that via \copy.  I put all the data points in memory up front rather than
generating it per-run on the fly, so that perl consumes about the same
amount of memory regardless of the run size.  You would want to use more
than 1..1e6 if you are on a very large RAM machine.

Something like:

for $run_size in 1 10 100 1000 1 10; do
  perl -le 'my @x; push @x, int(rand()*1e8) foreach 1..1e6; while (@x)
{print foreach sort {$a<=>$b} splice @x,0,'$run_size'; }'| time psql -c
'\copy foo from stdin';
done

But you probably want another inner loop so that the \copy gets executed
multiple times per run_size, so that each run_size executes for at least a
couple checkpoint cycles.

Cheers,

Jeff

Re: [HACKERS] pg_dump and pg_dumpall in real life

2013-11-11 Thread Josh Berkus

On 11/11/2013 03:06 PM, David Johnston wrote:
> Josh Berkus wrote
>> Well, then we just need pg_restore to handle the "role already exists"
>> error message gracefully.  That's all.  Or a "CREATE ROLE IF NOT EXISTS"
>> statement, and use that for roles.
> 
> My only qualm here is if the exists check is based off of role name only. 
> If database "A" and database "B" came from different clusters but both have
> a role "david" the actual identity of "david" is (could be) different
> because the source cluster.
> 
> The risk of such occurring is a high-security situation is likely to be
> small but some kind of "--ignore-different-cluster-same-role" flag may be
> worthwhile such that pg_restore will error unless that flag is set (i.e.,
> high security by default).  The error itself should be rare enough most
> people wouldn't even notice it is there but seeing such an error (with a
> hint provided as well) would be easily able to disable and continue on with
> the restore.

I'd do the opposite: let's optimize for the most common case, not the
least common one.  So we'd do --role-errors, which would throw a fatal
error on duplicate roles, instead of just posting a WARNING.

Again, this is all rather academic, unless you know someone who's eager
to dig into pg_dump/pg_restore.

-- 
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_dump and pg_dumpall in real life

2013-11-11 Thread David Johnston

Josh Berkus wrote
> Well, then we just need pg_restore to handle the "role already exists"
> error message gracefully.  That's all.  Or a "CREATE ROLE IF NOT EXISTS"
> statement, and use that for roles.

My only qualm here is if the exists check is based off of role name only. 
If database "A" and database "B" came from different clusters but both have
a role "david" the actual identity of "david" is (could be) different
because the source cluster.

The risk of such occurring is a high-security situation is likely to be
small but some kind of "--ignore-different-cluster-same-role" flag may be
worthwhile such that pg_restore will error unless that flag is set (i.e.,
high security by default).  The error itself should be rare enough most
people wouldn't even notice it is there but seeing such an error (with a
hint provided as well) would be easily able to disable and continue on with
the restore.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/pg-dump-and-pg-dumpall-in-real-life-tp518p5777823.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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

Re: [HACKERS] Clang 3.3 Analyzer Results

2013-11-11 Thread Kevin Grittner

Peter Geoghegan  wrote:
> Kevin Grittner  wrote:
>
>> I'm currently capturing a text version of all the warnings from
>> this.  Will gzip and post when it finishes.  It's generating a lot
>> of warnings; I have no idea how many are PostgreSQL problems and
>> how many are false positives; will just post the whole set FWIW.  I
>> am using the 3.4 development nightly snapshot with these commands:
>
> When I tried out scan-build a while ago, the results were kind of
> disappointing - there were lots of false positives. Clearly the tool
> was inferior to Coverity at that time. I'd be interested to see if
> there has been much improvement since.

Perhaps it will be of some value in terms of filing additional bug
reports with clang if it proves to have so many false positives
that it has little value in evaluating PostgreSQL code.

It does seem hard to believe that clang tools would find as enough
problems that were missed by Coverity and Valgrind to account for
all the warnings that are scrolling by; but it looks like it has
pointed out at least *one* problem that's worth fixing.

Ah, it finished.  Results attached; I haven't had time to review
them yet.

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

scan-build-results-1013-11-11.txt.gz
Description: GNU Zip compressed data

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

Re: [HACKERS] Clang 3.3 Analyzer Results

On Mon, Nov 11, 2013 at 5:51 PM, Peter Geoghegan  wrote:
> On Mon, Nov 11, 2013 at 2:45 PM, Jeffrey Walton  wrote:
>> I think you are right. Coverity is a very nice tool, and Clang has
>> some growing to do.
>
> To be fair to the LLVM/Clang guys, it's not as if static analysis is a
> very high priority for them.
Absolutely. I'm very impressed with the tool (especially the dynamic
checkers). And you can't beat the price.

I'd be happy to buy every one of LLVM/Clang devs a beer :)

Jeff


-- 
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] Clang 3.3 Analyzer Results

On Mon, Nov 11, 2013 at 5:18 PM, Kevin Grittner  wrote:
> [moving the discussion to pgsql-hackers]
>
> Jeffrey Walton  wrote:
>> ...
>> ##
>> # Sanitizers
>>
>> make distclean
>>
>> export DYLD_FALLBACK_LIBRARY_PATH=/usr/local/lib/clang/3.3/lib/darwin/
>> export CC=/usr/local/bin/clang
>> export CXX=/usr/local/bin/clang++
>> export CFLAGS="-g3 -fsanitize=address -fsanitize=undefined"
>> export CXXFLAGS="-g3 -fsanitize=address -fsanitize=undefined 
>> -fno-sanitize=vptr"
>>
>> ./configure
>>
>> make
>>
>> make check 2>&1 | asan_symbolize.py
>
> I haven't tried this yet, but will have a go at it after I capture
> the other.
Be sure asan_symbolize.py is on path. Otherwise, your dumps won't have
any symbols.

Jeff


-- 
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] Clang 3.3 Analyzer Results

2013-11-11 Thread Peter Geoghegan

On Mon, Nov 11, 2013 at 2:45 PM, Jeffrey Walton  wrote:
> I think you are right. Coverity is a very nice tool, and Clang has
> some growing to do.

To be fair to the LLVM/Clang guys, it's not as if static analysis is a
very high priority for them.


-- 
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] pg_dump and pg_dumpall in real life

2013-11-11 Thread David Johnston

Andrew Dunstan wrote
> A general ability to rename things would be good. In particular,
> restoring schema x into schema y or table x into table y would be very
> useful, especially if you need to be able to compare old with new.

compare old and new what? I would imagine that schema comparisons would be
much easier if the only thing that is different is the database name and you
compare database "old" to database "new".

Are there any existing threads or posts, that you recollect, that detail
solid use-cases for "clone-and-rename" mechanics? I don't seem to recall
anything in the past year or so but my coverage is probably only about 70%
in that timeframe.

SQL seems particularly unfriendly to renaming and runtime name resolution in
general (largely due to caching effects). Some kind of alias mechanism
makes sense conceptually but the performance hit for such isn't likely to be
worth incurring.

I could see having table name aliases so that raw data in a dump from one
database could be restored into another but I'd likely require that the user
be able to generate the target schema from source themselves. That would
facilitate the use-case where the DBA/programmer is able to fully recreate
their schema from source and only require that actual data be restored into
the newly created database. I can see where grants may fall into a grey
middle-area but functions/view/triggers and the like would need to be
synchronized with any schema naming changes and that should, IMO, be driven
from source and not facilitated by a dump/restore process.

David J.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/pg-dump-and-pg-dumpall-in-real-life-tp518p5777816.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.

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

Re: [HACKERS] Clang 3.3 Analyzer Results

On Mon, Nov 11, 2013 at 5:29 PM, Peter Geoghegan  wrote:
> On Mon, Nov 11, 2013 at 2:18 PM, Kevin Grittner  wrote:
>> I'm currently capturing a text version of all the warnings from
>> this.  Will gzip and post when it finishes.  It's generating a lot
>> of warnings; I have no idea how many are PostgreSQL problems and
>> how many are false positives; will just post the whole set FWIW.  I
>> am using the 3.4 development nightly snapshot with these commands:
>
> When I tried out scan-build a while ago, the results were kind of
> disappointing - there were lots of false positives. Clearly the tool
> was inferior to Coverity at that time. I'd be interested to see if
> there has been much improvement since.
I think you are right. Coverity is a very nice tool, and Clang has
some growing to do. For example, the Clang analyzer does not
[currently] do inter-translation unit analysis. So the following will
cause a false alarm:

// test-1.c
int n;
IntializeN(&n);
DoSomethingWithN(n);

// test-2.c
IntializeN(int* n) { if(n) {*n = 5;} }

On the other hand, its easy to accommodate the analyzer because (1)
programmers are smart, and (2) analyzers are dumb. So the following
would be an easy work around to reduce the noise:

int n = 0;
IntializeN(&n);

If the assignment is extraneous, then the optimizer will remove it and
there's no performance penalty. So its no big deal and it cuts down on
the time wasted on the false positives.

Otherwise, you get into a scenario where the tool is not used. That's
a shame since we know some of its findings are legitimate.

In the end, I don't think its wise to throw the baby out with the bath
water. Learn to work with the tools, becuase the code and users will
benefit.

Jeff

-- 
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] Clang 3.3 Analyzer Results

2013-11-11 Thread Peter Geoghegan

On Mon, Nov 11, 2013 at 2:18 PM, Kevin Grittner  wrote:
> I'm currently capturing a text version of all the warnings from
> this.  Will gzip and post when it finishes.  It's generating a lot
> of warnings; I have no idea how many are PostgreSQL problems and
> how many are false positives; will just post the whole set FWIW.  I
> am using the 3.4 development nightly snapshot with these commands:

When I tried out scan-build a while ago, the results were kind of
disappointing - there were lots of false positives. Clearly the tool
was inferior to Coverity at that time. I'd be interested to see if
there has been much improvement since.

One thing I noticed at the time was that the tool didn't have any
gumption about elog() and control flow, even though IIRC at that time
we had the abort() trick (see commit
71450d7fd6c7cf7b3e38ac56e363bff6a681973c). I seem to also recall
Coverity correctly handling that, although perhaps I'm unfairly
crediting them with taking advantage of the abort() trick because of
the state of Postgres when I tried each of those two tools - it might
be that scan-build *would* have taken advantage of that at the time,
if only the trick was there.

-- 
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] pg_dump and pg_dumpall in real life

2013-11-11 Thread Josh Berkus

On 11/11/2013 06:24 AM, Stephen Frost wrote:
> * Rafael Martinez (r.m.guerr...@usit.uio.no) wrote:
>> * We need a pg_dump solution that can generate in one step all the
>> necessary pieces of information needed when restoring or cloning a
>> database. (schema, data, privileges, users and alter database/role data)
> 
> This sounds pretty reasonable and should be possible to implement- but
> realize that, on the restore side, you might end up with multiple
> attempts to create the same objects.  Consider a role that's depended
> upon by objects in two databases- it would be included in the dump of
> both of those databases and if you restored both of those into the same
> cluster, one of the CREATE ROLE statements would fail.

Well, then we just need pg_restore to handle the "role already exists"
error message gracefully.  That's all.  Or a "CREATE ROLE IF NOT EXISTS"
statement, and use that for roles.

>> * It would be great to be able to tell pg_restore that user1 in the
>> dump will became user2 in the restored/cloned database. The same for
>> the name of the database.
> 
> This is a lot uglier, unfortunately.  We've seen this multiple times
> before- there's not a good way to provide such a mapping as a command
> line option.  There may also be issues with the dependency resolution..

This sounds like one of the reasons we still *have* text-mode dumps.
For stuff like this.

> As for 'what we need', I'd think someone with the time and energy to
> write the patch and work with the community to implement it..

+1

-- 
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] Clang 3.3 Analyzer Results

2013-11-11 Thread Kevin Grittner

[moving the discussion to pgsql-hackers]

Jeffrey Walton  wrote:

> The Analyzer is invoked with scan-build. Its used when compiling
> the package because it performs static analysis.
>
> The Santizers are invoked with the runtime flags. They are used
> with the `check` program because they perform dynamic analysis.
> The more self test the better.

Thanks for the recipes!

> ##
> # Scan-view
> 
> make distclean
> 
> export CC="/usr/local/bin/clang"
> export CXX="/usr/local/bin/clang++"
> 
> /usr/local/bin/scan-build/scan-build --use-analyzer=/usr/local/bin/clang 
> ./configure
> 
> /usr/local/bin/scan-build/scan-build --use-analyzer=/usr/local/bin/clang make

I'm currently capturing a text version of all the warnings from
this.  Will gzip and post when it finishes.  It's generating a lot
of warnings; I have no idea how many are PostgreSQL problems and
how many are false positives; will just post the whole set FWIW.  I
am using the 3.4 development nightly snapshot with these commands:

scan-build --use-analyzer=/usr/bin/clang ./configure --silent 
--prefix=$PWD/Debug --enable-debug --enable-cassert --enable-depend 
--with-libxml --with-libxslt --with-openssl --with-perl --with-python
scan-build --use-analyzer=/usr/bin/clang make -s world

> ##
> # Sanitizers
> 
> make distclean
> 
> export DYLD_FALLBACK_LIBRARY_PATH=/usr/local/lib/clang/3.3/lib/darwin/
> export CC=/usr/local/bin/clang
> export CXX=/usr/local/bin/clang++
> export CFLAGS="-g3 -fsanitize=address -fsanitize=undefined"
> export CXXFLAGS="-g3 -fsanitize=address -fsanitize=undefined 
> -fno-sanitize=vptr"
> 
> ./configure
> 
> make
> 
> make check 2>&1 | asan_symbolize.py

I haven't tried this yet, but will have a go at it after I capture
the other.

--
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] stats for network traffic WIP

2013-11-11 Thread Nigel Heron

On Thu, Nov 7, 2013 at 8:21 PM, Greg Stark  wrote:
>
>
> The most interesting thing that I could see calculating from these stats
> would require also knowing how much time was spent waiting on writes and
> reads on the network. With the cumulative time spent as well as the count of
> syscalls you can calculate the average latency over any time period between
> two snapshots. However that would involve adding two gettimeofday calls
> which would be quite likely to cause a noticeable impact on some
> architectures. Unless there's already a pair of gettimeofday calls you can
> piggy back onto?
>
>

Adding timing instrumentation to each send() and recv() would require
over 50 calls to gettimeofday for a simple psql -c "SELECT 1", while
the client was waiting. That would add ~40usec extra time (estimated
using pg_test_timing on my laptop without TSC). It might be more
overhead than it's worth.

-nigel.


-- 
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] [v9.4] row level security

On Thu, Nov 7, 2013 at 9:08 PM, Craig Ringer  wrote:
> On 11/07/2013 09:47 PM, Greg Stark wrote:
>> Incidentally I still feel this is at root the problem with updateable
>> views in general. I know it's a bit off to be tossing in concerns from
>> the peanut gallery when I'm not actually offering to work on it and
>> others are having putting in serious efforts in this area and having
>> some success. So take this for what it's worth...
>
> Frankly, the peanut gallery input can be quite handy. It's easy to get
> so stuck in the way you've seen it thought about already that you don't
> see other ways to view it. Plus, sometimes the peanut gallery becomes
> the "oh, I don't like this at all" crowd when commit time is
> approaching, so early comments are better than no comments then last
> minute complaints.
>
>> I think the right approach for updateable views would be to support a
>> syntax like this in the planner:
>>
>> UPDATE (select * from tab1,tab2 ...) WHERE tab1.id  = ..
>> SET ...
>
> I want to support that for rewritten parse trees, and therefore (because
> of recursive rewrite) in pre-rewrite parse trees. It's exactly what's
> needed to make this sane, IMO, and I think this is what Robert was
> suggesting with making UPDATE capable of dealing with operating directly
> on a subquery scan.
>
> I'm not at all convinced it should be exposed to the user and accepted
> by the parser as SQL, but I don't know if that's what you were suggesting.
>
> Robert? Is this what you meant? If so, any chance you can point a
> planner neophyte like me in vaguely the right direction?

I haven't studied this issue well enough to know what's really needed
here, but Dean Rasheed's approach sounded like a promising tack to me.

-- 
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] [v9.4] row level security

On Wed, Nov 6, 2013 at 1:38 AM, Craig Ringer  wrote:
> (a) Updatable views are implemented in the rewriter, not the planner.
> The rewriter is not re-run when plans are invalidated or when the
> session authorization changes, etc. This means that we can't simply omit
> the RLS predicate for superuser because the same rewritten parse tree
> might get used for both superuser and non-superuser queries.

My impression was that we had discussed this problem with respect to
some earlier version of the RLS patch and that the conclusion of that
discussion was that we needed to record in the cached plan whether the
plan was one which is sensitive to the user ID and, if so, avoid using
that plan with a different user ID.  I am murky on the details; I
believe the original discussion of this topic was a year or more ago.

> (b) Inheritance is a problem when RLS is done in the rewriter. As I
> understood it from Kohei KaiGai's description to me earlier, there was a
> strong preference on -hackers to enforce RLS predicates for child and
> parent tables completely independently.

Not to put a too fine a point on it, but I think that's a really bad
plan; and I don't remember any such discussion.

> That's how RLS currently works,
> but it might be hard to get the same effect when applying RLS in the
> rewriter. We'd need to solve that, or redefine RLS's behaviour so that
> the predicate on a parent table applies to any child tables too.
> Personally I'd prefer the latter.

Yes, let's please redefine it.  The goal here ought to be to make RLS
work as smoothly as possible with the rest of the system, not to
invent weird semantics that are both unlike what we do elsewhere - and
difficult to implement, to boot.  I thought the whole point of
implementing security barrier views was that read-side RLS would work
just the same way, not having randomly incompatible semantics.

-- 
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] alter_table regression test problem



Robert Haas  schrieb:
>On Mon, Nov 11, 2013 at 4:00 PM, Robert Haas 
>wrote:
>> On Thu, Nov 7, 2013 at 12:18 PM, Andres Freund
> wrote:
>>> On 2013-11-07 10:10:34 -0500, Tom Lane wrote:
 Andres Freund  writes:
 > On 2013-11-07 06:49:58 -0800, Kevin Grittner wrote:
 >> It's up to the committer whether to indent
 >> after review and make both substantive and whitespace changes
 >> together, but I have definitely seen those done separately, or
>even
 >> left for the next global pgindent run to fix.

 > Hm. I don't remember many such cases and I've just looked across
>the git
 > history and i didn't really find anything after
 > a04161f2eab55f72b3c3dba9baed0ec09e7f633f, but that was back when
 > individuals couldn't run pgindent because of the typedefs file.

 FWIW, I tend to pgindent before committing, now that it's easy to
>do so.
 But in cases like this, I'd much rather review the patch *before*
>that
 happens.  Basically the point of the review is to follow and
>confirm
 the patch author's thought process, and I'll bet you put the braces
>in
 before reindenting too.
>>>
>>> Well, I did both at the same time, I have an emacs command for that
>>> ;). But independent from that technicality, reindenting is part of
>>> changing the flow of logic for me - I've spent a couple of years
>>> primarily writing python (where indentation is significant), maybe
>it's
>>> that.
>>>
>>> So, here's the patch (slightly editorialized) with reverted
>indenting of
>>> that block.
>>
>> Gah.  Well, of course, I have the opposite preference from Tom on how
>> this should be indented.  Sigh.
>
>...and I hit send too soon.
>
>I'm pretty sure that the current coding, which blows away the whole
>relation, is used in other places, and I really don't see why it
>should be fundamentally flawed, or why we should change it to clear
>the cache entries out one by one instead of en masse.
>RelidByRelfilenode definitely needs to use HASH_FIND rather than
>HASH_ENTER, so that part I agree with.

It surely is possible to go that route, but imagine what happens if the 
heap_open() blows away the entire hash. We'd either need to recheck if the hash 
exists before entering or recreate it after dropping. It seemed simpler to 
follow attoptcache's example.

Regards,

Andres

-- 
Please excuse brevity and formatting - I am writing this on my mobile phone.

Andres Freund  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


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

Re: [HACKERS] Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

2013-11-11 Thread Pavel Stehule

2013/11/11 Pavel Stehule 

>
>
>
> 2013/11/11 Tom Lane 
>
>> Andres Freund  writes:
>> > Turns out that's bogus - ALTER TABLE has two levels of NOT EXISTS.
>>
>> > Maybe we should just do the same for DROP TRIGGER?
>>
>> > DROP TRIGGER [ IF EXISTS ] name ON table_name [ IF EXISTS ] [ CASCADE |
>> RESTRICT ]
>>
>
This syntax is not consistent with other IF EXISTS.

should be (IF EXISTS is before name always)

DROP TRIGGER [ IF EXISTS ] name ON [ IF EXISTS ] table_name  [ CASCADE |
RESTRICT ]

What do you think about?

Regards

Pavel



>
>> Works for me.
>>
>
> for me too
>
> tomorrow I'll prepare patch
>
> Regards
>
> Pavel
>
>
>>
>> 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] alter_table regression test problem

On Mon, Nov 11, 2013 at 4:00 PM, Robert Haas  wrote:
> On Thu, Nov 7, 2013 at 12:18 PM, Andres Freund  wrote:
>> On 2013-11-07 10:10:34 -0500, Tom Lane wrote:
>>> Andres Freund  writes:
>>> > On 2013-11-07 06:49:58 -0800, Kevin Grittner wrote:
>>> >> It's up to the committer whether to indent
>>> >> after review and make both substantive and whitespace changes
>>> >> together, but I have definitely seen those done separately, or even
>>> >> left for the next global pgindent run to fix.
>>>
>>> > Hm. I don't remember many such cases and I've just looked across the git
>>> > history and i didn't really find anything after
>>> > a04161f2eab55f72b3c3dba9baed0ec09e7f633f, but that was back when
>>> > individuals couldn't run pgindent because of the typedefs file.
>>>
>>> FWIW, I tend to pgindent before committing, now that it's easy to do so.
>>> But in cases like this, I'd much rather review the patch *before* that
>>> happens.  Basically the point of the review is to follow and confirm
>>> the patch author's thought process, and I'll bet you put the braces in
>>> before reindenting too.
>>
>> Well, I did both at the same time, I have an emacs command for that
>> ;). But independent from that technicality, reindenting is part of
>> changing the flow of logic for me - I've spent a couple of years
>> primarily writing python (where indentation is significant), maybe it's
>> that.
>>
>> So, here's the patch (slightly editorialized) with reverted indenting of
>> that block.
>
> Gah.  Well, of course, I have the opposite preference from Tom on how
> this should be indented.  Sigh.

...and I hit send too soon.

I'm pretty sure that the current coding, which blows away the whole
relation, is used in other places, and I really don't see why it
should be fundamentally flawed, or why we should change it to clear
the cache entries out one by one instead of en masse.
RelidByRelfilenode definitely needs to use HASH_FIND rather than
HASH_ENTER, so that part I agree 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

Re: [HACKERS] alter_table regression test problem

On Thu, Nov 7, 2013 at 12:18 PM, Andres Freund  wrote:
> On 2013-11-07 10:10:34 -0500, Tom Lane wrote:
>> Andres Freund  writes:
>> > On 2013-11-07 06:49:58 -0800, Kevin Grittner wrote:
>> >> It's up to the committer whether to indent
>> >> after review and make both substantive and whitespace changes
>> >> together, but I have definitely seen those done separately, or even
>> >> left for the next global pgindent run to fix.
>>
>> > Hm. I don't remember many such cases and I've just looked across the git
>> > history and i didn't really find anything after
>> > a04161f2eab55f72b3c3dba9baed0ec09e7f633f, but that was back when
>> > individuals couldn't run pgindent because of the typedefs file.
>>
>> FWIW, I tend to pgindent before committing, now that it's easy to do so.
>> But in cases like this, I'd much rather review the patch *before* that
>> happens.  Basically the point of the review is to follow and confirm
>> the patch author's thought process, and I'll bet you put the braces in
>> before reindenting too.
>
> Well, I did both at the same time, I have an emacs command for that
> ;). But independent from that technicality, reindenting is part of
> changing the flow of logic for me - I've spent a couple of years
> primarily writing python (where indentation is significant), maybe it's
> that.
>
> So, here's the patch (slightly editorialized) with reverted indenting of
> that block.

Gah.  Well, of course, I have the opposite preference from Tom on how
this should be indented.  Sigh.


-- 
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] subquery q2 does not have attribute 0

Kevin Grittner  writes:
> I get:
> ERROR:  subquery q2 does not have attribute 0

> I checked and found it broken on 9.2 and 9.3, but working on 9.1. 
> git bisect, says it was broken by commit
> 1cb108efb0e60d87e4adec38e7636b6e8efbeb57.

My fault, eh?  Will look.

regards, tom lane


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

[HACKERS] subquery q2 does not have attribute 0

2013-11-11 Thread Kevin Grittner

In playing with a sample query from another thread I found this
query is broken on the master branch:

select q1.*
  from (select 'a'::text) q1(c)
  where not exists
   (select * from (select 'A'::text) q2(c) where q2 = q1);

I get:

ERROR:  subquery q2 does not have attribute 0

I checked and found it broken on 9.2 and 9.3, but working on 9.1. 
git bisect, says it was broken by commit
1cb108efb0e60d87e4adec38e7636b6e8efbeb57.

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

Re: [HACKERS] Add %z support to elog/ereport?

Andres Freund  writes:
> On 2013-11-11 12:31:55 -0500, Robert Haas wrote:
>> I seem to recall that our %m support involves rewriting the error
>> string twice, which I think is actually kind of expensive if, for
>> example, you've got a loop around a PL/pgsql EXCEPTION block.

> Yes, it does that. Is that actually where a significant amount of time
> is spent? I have a somewhat hard time believing that.

I don't see any double copying.  There is *one* copy made by
expand_fmt_string.  Like Andres, I'd want to see proof that
expand_fmt_string is a significant time sink before we jump through
these kinds of hoops to get rid of it.  It looks like a pretty cheap
loop to me.  (It might be less cheap if we made it smart enough to
identify 'z' flags, though :-()

>> I'd
>> actually like to find a way to get rid of the existing %m support,
>> maybe by having a flag that says "oh, and by the way append the system
>> error to my format string"; or by changing %m to %s and having the
>> caller pass system_error_string() or similar for that format position.

The first of these doesn't work unless you require translations to
assemble the string the same way the English version does.  The second
would work, I guess, but it'd sure be a pain to convert everything.

regards, tom lane

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

Re: [HACKERS] Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

2013-11-11 Thread Pavel Stehule

2013/11/11 Tom Lane 

> Andres Freund  writes:
> > Turns out that's bogus - ALTER TABLE has two levels of NOT EXISTS.
>
> > Maybe we should just do the same for DROP TRIGGER?
>
> > DROP TRIGGER [ IF EXISTS ] name ON table_name [ IF EXISTS ] [ CASCADE |
> RESTRICT ]
>
> Works for me.
>

for me too

tomorrow I'll prepare patch

Regards

Pavel


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

Re: [HACKERS] Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

2013-11-11 Thread Pavel Stehule

I can agree, so DROP TRIGGER doesn't need a IF EXISTS clause when it is
executed after DROP TABLE.

pg_dump -c produces:

DROP TRIGGER jjj ON public.foo;
DROP TABLE public.foo;
DROP FUNCTION public.f1();
DROP EXTENSION plpgsql;
DROP SCHEMA public;

Is there some reason why we use explicitly DROP TRIGGER there?

Regards

Pavel




2013/10/15 Andres Freund 

> On 2013-10-15 00:23:15 +0200, Tomas Vondra wrote:
> > Hi,
> >
> > On 14.10.2013 23:44, Andres Freund wrote:
> > > On 2013-10-10 12:54:23 -0400, Andrew Dunstan wrote:
> > >> On 09/19/2013 06:12 PM, Pavel Stehule wrote:
> > >>> 2013/9/16 Satoshi Nagayasu  > >>> >
> > >>>
> > >>> I'm looking at this patch, and I have a question here.
> > >>>
> > >>> Should "DROP TRIGGER IF EXISTS" ignore error for non-existing
> > >>> trigger and non-existing table? Or just only for non-existing
> > >>> trigger?
> > >>>
> > >>> My opinion is so, both variants should be ignored - it should be
> > >>> fully fault tolerant in this use case.
> > >>
> > >> This thread seems to have gone cold, but I'm inclined to agree with
> > >> Pavel. If the table doesn't exist, neither does the trigger, and
> > >> the whole point of the 'IF EXISTS' variants is to provide the
> > >> ability to issue DROP commands that don't fail if their target is
> > >> missing.
> > >
> > > -1, this seems to likely to just hide typos.
> >
> > Not sure I agree with your reasoning. Isn't that equally true for 'IF
> > EXISTS' clause with all commands in general? Why should we use "likely
> > to hide typos" argument in this case and not the others?
>
> Because there simply is no reason to issue a DROP TRIGGER IF EXISTS if
> you don't need the contents of the table. In that case you can just
> issue a DROP TABLE IF EXISTS and start anew.
>
> > The purpose of this patch was to add support for quiet "pg_restore
> > --clean" and pg_restore should not do typos (if it does, we're in much
> > deeper troubles I guess).
>
> Why does that even have to do anything for triggers? Emitting DROP TABLE
> should be enough.
>
> Greetings,
>
> Andres Freund
>
> --
>  Andres Freund http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] all_visible replay aborting due to uninitialized pages

Bruce Momjian  writes:
> On Mon, Nov 11, 2013 at 01:42:07AM +0100, Andres Freund wrote:
>> The fix is included in 9.2.5, it's just not noted in the release notes.

> Yes, I missed it because I didn't understand the importance of these
> commit messages:

>   commit 17fa4c321ccf9693de406faffe6b235e949aa25f
>   Author: Robert Haas 
>   Date:   Thu Jun 6 10:15:45 2013 -0400

>   Ensure that XLOG_HEAP2_VISIBLE always targets an initialized page.

>   Andres Freund

I would say that's not your fault, it's the fault of the author of the
commit message.  These messages are supposed to provide enough information
for release note writing.  This one seems a bit ... um ... terse.

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] regclass error reports improperly downcased

Jim Nasby  writes:
> Is anyone opposed to some kind of hint?

Would depend on the text of the hint.  I'm a bit dubious that we can
come up with something that's not wildly inappropriate in other scenarios.

regards, tom lane


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

Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix whitespace issues found by git diff --check, add gitattribut

2013-11-11 Thread Andrew Dunstan



On 11/11/2013 02:17 PM, Peter Eisentraut wrote:

On 11/11/13, 10:26 AM, Tom Lane wrote:

Peter Eisentraut  writes:

Fix whitespace issues found by git diff --check, add gitattributes
Set per file type attributes in .gitattributes to fine-tune whitespace
checks.  With the associated cleanups, the tree is now clean for git

Hmm, I thought the .gitattributes file would prevent this:

$ git diff --staged --check
src/test/regress/expected/join.out:3110: trailing whitespace.
+ q1 | q2 | f1 | ff

Is there something I have to do to configure git to honor the file?

(In case it matters, this is git 1.7.1, which is what Red Hat is
shipping these days in RHEL6.)

Older versions of git (before 1.8.2) do not support the "**" syntax used
here:

**/expected/*.out   -whitespace

If we're slightly daring, we could change this to just

*.out   -whitespace

and analogously for the other entries, but I haven't fully analyzed that.





Can we please agree on a minimum version of git and just support its 
features? Relying on versions close to the bleeding edge affects a lot 
of people - specifically it potentially affects every buildfarm member 
as well as every developer.


cheers

andrew


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

Re: [HACKERS] all_visible replay aborting due to uninitialized pages

2013-11-11 Thread Bruce Momjian

On Mon, Nov 11, 2013 at 01:42:07AM +0100, Andres Freund wrote:
> Hi,
> 
> On 2013-11-10 17:40:31 -0700, Noah Yetter wrote:
> > Like your customer, this bug has blown up my standby servers, twice in the
> > last month: the first time all 4 replicas, the second time (mysteriously
> > but luckily) only 1 of them.
> > 
> > At any rate, since the fix isn't available yet, is/are there any
> > configuration changes that can be made or maintenance procedures that can
> > be undertaken to prevent or reduce the probability of this bug popping up
> > again in the meantime?  I really can't afford to be without my standby
> > servers during the holidays, even for the few hours it takes to build a new
> > one.
> 
> The fix is included in 9.2.5, it's just not noted in the release notes.

Yes, I missed it because I didn't understand the importance of these
commit messages:

commit 17fa4c321ccf9693de406faffe6b235e949aa25f
Author: Robert Haas 
Date:   Thu Jun 6 10:15:45 2013 -0400

Ensure that XLOG_HEAP2_VISIBLE always targets an initialized page.

Andres Freund

commit 4c641d994e19676ef2fec574d52d2156ffc2b3ce
Author: Robert Haas 
Date:   Thu Jun 6 10:14:46 2013 -0400

Backport log_newpage_buffer.

Andres' fix for XLOG_HEAP2_VISIBLE on unitialized pages requires
this.

Sorry.

-- 
  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] Fwd: Test of Algorithm || Indexing Scheme

Hi,

Actually,

I want to test an algorithm in which I will use store tuple_id in some
other data structure and value of the key of index will contain some random
created by me. Could you please tel me which file need to be change for it.

you said something about usage of GiST opclass. Can you please elaborate ur
opinion in detail. It would be of a great help to me.

Regards,
Rohit

On Mon, Nov 11, 2013 at 6:16 PM, Rohit Goyal  wrote:

> Hi,
> Can you pls tel me how can achieve which sound pretty exotic to you.
>
> Can u please share some relevant documents.  I havw no clue from where to
> start.
>
> Regards
> Rohit
> On 11.11.2013 14:12, Rohit Goyal wrote:
>
>> Hi,
>> Thanks for reply.
>> I actually want to make some changes in all operations on index like
>> insert, update, delete.
>> for example, i want store a new customized value for every key inserted in
>> b tree instead of storing Tuple Id as value.
>>
>
> That sounds pretty exotic, I can't imagine what good the index will do if
> it doesn't contain tuple IDs.
>
>  Can you also pls explain me more about appoach to follow to use my
>> algorithm as GiST opclass. I dont have much knowledge abt this also?
>>
>
> See user manual, and the examples in the server code and contrib.
>
> - Heikki
>

-- 
Regards,
Rohit Goyal

[HACKERS] Re: [COMMITTERS] pgsql: Fix whitespace issues found by git diff --check, add gitattribut

2013-11-11 Thread Peter Eisentraut

On 11/11/13, 10:26 AM, Tom Lane wrote:
> Peter Eisentraut  writes:
>> Fix whitespace issues found by git diff --check, add gitattributes
>> Set per file type attributes in .gitattributes to fine-tune whitespace
>> checks.  With the associated cleanups, the tree is now clean for git
> 
> Hmm, I thought the .gitattributes file would prevent this:
> 
> $ git diff --staged --check
> src/test/regress/expected/join.out:3110: trailing whitespace.
> + q1 | q2 | f1 | ff 
> 
> Is there something I have to do to configure git to honor the file?
> 
> (In case it matters, this is git 1.7.1, which is what Red Hat is
> shipping these days in RHEL6.)

Older versions of git (before 1.8.2) do not support the "**" syntax used
here:

**/expected/*.out   -whitespace

If we're slightly daring, we could change this to just

*.out   -whitespace

and analogously for the other entries, but I haven't fully analyzed that.



-- 
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] logical changeset generation v6.5

On 2013-11-10 14:45:17 -0500, Steve Singer wrote:
> On 11/10/2013 09:41 AM, Andres Freund wrote:
> >Still give me the following:
> >update  disorder.do_inventory set ii_in_stock=2 where ii_id=251;
> >UPDATE 1
> >test1=# LOG:  tuple in table with oid: 35122 without primary key
> >Hm. Could it be that you still have an older "test_decoding" plugin
> >lying around? The current one doesn't contain that string
> >anymore. That'd explain the problems.
> >In v6.4 the output plugin API was changed that plain heaptuples are
> >passed for the "old" key, although with non-key columns set to
> >NULL. Earlier it was a "index tuple" as defined by the indexes
> >TupleDesc.
> 
> Grrr, yah that was the problem I had compiled but not installed the newer
> plugin. Sorry.

Heh, happened to me several times during development ;)

> >>Which I suspect means oldtuple is back to null
> >Which is legitimate though, if you don't update the primary (or
> >explicitly chosen candidate) key. Those only get logged if there's
> >actual changes in those columns.
> >Makes sense?
> Is the expectation that plugin writters will call
> RelationGetIndexAttrBitmap(relation,INDEX_ATTR_BITMAP_IDENTITY_KEY);
> to figure out what the identity key is.

I'd expect them to check whether relreplident is FULL, NOTHING or
DEFAULT|INDEX. In the latter case they can check
Relation->rd_replidindex. The bitmap doesn't really seem to be helpful?

> How do we feel about having the decoder logic populate change.oldtuple with
> the identity  on UPDATE statements when it is null?

Not really keen - that'd be a noticeable overhead. Note that in the
cases where DEFAULT|INDEX is used, you can just use the new tuple to
extract what you need for the pkey lookup since they now have the same
format and since it's guaranteed that the relevant columns haven't
changed if oldtup is null and there's a key.

What are you actually doing with those columns? Populating a WHERE
clause?

Greetings,

Andres Freund

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


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

Re: [HACKERS] better atomics - spinlock fallback?

Hi,

Instead of de-supporting platforms that don't have CAS support or
providing parallel implementations we could relatively easily build a
spinlock based fallback using the already existing requirement for
tas().
Something like an array of 16 spinlocks, indexed by a more advanced
version of ((char *)(&atomics) >> sizeof(char *)) % 16. The platforms
that would fallback aren't that likely to be used under heavy
concurrency, so the price for that shouldn't be too high.

The only real problem with that would be that we'd need to remove the
spinnlock fallback for barriers, but that seems to be pretty much
disliked.

Thoughts?

Greetings,

Andres Freund

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


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

Re: [HACKERS] regclass error reports improperly downcased

2013-11-11 Thread Jim Nasby


On 11/8/13 2:21 PM, Tom Lane wrote:

Jim Nasby  writes:

Ahh, duh. Hrm... I ran across this because someone here got confused by this:



SELECT pg_total_relation_size( schema_name || '.' || relname ) FROM 
pg_stat_all_tables
ERROR: relation "moo" does not exist


Personally I'd do that like

   select pg_total_relation_size(oid) from pg_class where ...

and avoid fooling with regclass conversion at all.


Yeah, that's what I did in this case. I'm just trying to make it more obvious 
to users that make this mistake.

Is anyone opposed to some kind of hint?
--
Jim Nasby, Lead Data Architect   (512) 569-9461


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

Re: [HACKERS] Add %z support to elog/ereport?

On 2013-11-11 12:31:55 -0500, Robert Haas wrote:
> On Mon, Nov 11, 2013 at 10:50 AM, Andres Freund  
> wrote:
> > Hi,
> >
> > I'd like to add support for the length modifier %z. Linux' manpages
> > describes it as:
> >  z  A  following  integer conversion corresponds to a size_t or ssize_t 
> > argument.
> >
> > Since gcc's printf format checks understand it, we can add support for
> > it similar to the way we added %m support.
> 
> I seem to recall that our %m support involves rewriting the error
> string twice, which I think is actually kind of expensive if, for
> example, you've got a loop around a PL/pgsql EXCEPTION block.

Yes, it does that. Is that actually where a significant amount of time
is spent? I have a somewhat hard time believing that.

> I'd
> actually like to find a way to get rid of the existing %m support,
> maybe by having a flag that says "oh, and by the way append the system
> error to my format string"; or by changing %m to %s and having the
> caller pass system_error_string() or similar for that format position.

I'd rather get our own printf implementation from somewhere before doing
that which would quite likely result in a bigger speedup besides the
significant portability improvments.

Greetings,

Andres Freund

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


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

Re: [HACKERS] Add %z support to elog/ereport?

On 2013-11-11 12:18:46 -0500, Tom Lane wrote:
> Andres Freund  writes:
> > gettext has support for it afaics, it's part of POSIX:
> 
> Really?  [ pokes around at pubs.opengroup.org ]  Hm, I don't see it
> in Single Unix Spec v2 (1997), but it is there in POSIX issue 7 (2008).
> Also, the POSIX page says it defers to the C standard, and I see it
> in C99.  Too bad not all our platforms are C99 yet :-(

Seems to be 2001:
http://pubs.opengroup.org/onlinepubs/007904975/functions/fprintf.html

Even though the date says it's from 2004, it's IEEE Std 1003.1 + minor
errata.


> Actually this raises an interesting testing question: how sure are we
> that there aren't already some format strings in the code that depend
> on C99 additions to the printf spec?  If they're not in code paths
> exercised by the regression tests, I'm not sure we'd find out from
> the buildfarm.

I agree, it's problematic. Especially as such code is likely to be in
error paths. I seem to recall you fixing some occurances you found
manually some time back so we clearly don't have an automated process
for it yet.

Greetings,

Andres Freund

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


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

Re: [HACKERS] Add %z support to elog/ereport?

On Mon, Nov 11, 2013 at 10:50 AM, Andres Freund  wrote:
> Hi,
>
> I'd like to add support for the length modifier %z. Linux' manpages
> describes it as:
>  z  A  following  integer conversion corresponds to a size_t or ssize_t 
> argument.
>
> Since gcc's printf format checks understand it, we can add support for
> it similar to the way we added %m support.

I seem to recall that our %m support involves rewriting the error
string twice, which I think is actually kind of expensive if, for
example, you've got a loop around a PL/pgsql EXCEPTION block.  I'd
actually like to find a way to get rid of the existing %m support,
maybe by having a flag that says "oh, and by the way append the system
error to my format string"; or by changing %m to %s and having the
caller pass system_error_string() or similar for that format position.

-- 
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] Add %z support to elog/ereport?

On 2013-11-11 12:01:40 -0500, Tom Lane wrote:
> Andres Freund  writes:
> >> I'm less than sure that every version of gcc will recognize %z, either
> >> ...
> 
> > It's been in recognized in 2.95 afaics, so I think we're good.

Hm. Strange. Has to have been backpatched to the ancient debian I have
around. Unfortunately I can't easily "apt-get source" there...

The commit that added it to upstream is:
commit 44e9fa656d60bb19ab81d76698a61e47a4b0857c
Author: drepper 
Date:   Mon Jan 3 21:48:49 2000 +

(format_char_info): Update comment.  (check_format_info): Recognize 'z'
modifier in the same way 'Z' was recognized.  Emit warning for formats
new in ISO C99 only if flag_isoc9x is not set.

git-svn-id: svn+ssh://gcc.gnu.org/svn/gcc/trunk@31188 
138bc75d-0d04-0410-961f-82ee72b054a4

That's 3.0. Verified it in the 3.0. tarball, although I didn't compile
test it.

> We might be willing to toss 2.95 overboard by now, but we'd need to be
> sure of exactly what the new minimum usable version is.

Well, we don't even need to toss it overboard, just live with useless
warnings there since we'd translate it ourselves.

Greetings,

Andres Freund

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

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

Re: [HACKERS] Add %z support to elog/ereport?

Andres Freund  writes:
> gettext has support for it afaics, it's part of POSIX:

Really?  [ pokes around at pubs.opengroup.org ]  Hm, I don't see it
in Single Unix Spec v2 (1997), but it is there in POSIX issue 7 (2008).
Also, the POSIX page says it defers to the C standard, and I see it
in C99.  Too bad not all our platforms are C99 yet :-(

Actually this raises an interesting testing question: how sure are we
that there aren't already some format strings in the code that depend
on C99 additions to the printf spec?  If they're not in code paths
exercised by the regression tests, I'm not sure we'd find out from
the buildfarm.

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] Fwd: Test of Algorithm || Indexing Scheme

Hi,
Can you pls tel me how can achieve which sound pretty exotic to you.

Can u please share some relevant documents.  I havw no clue from where to
start.

Regards
Rohit
On 11.11.2013 14:12, Rohit Goyal wrote:

> Hi,
> Thanks for reply.
> I actually want to make some changes in all operations on index like
> insert, update, delete.
> for example, i want store a new customized value for every key inserted in
> b tree instead of storing Tuple Id as value.
>

That sounds pretty exotic, I can't imagine what good the index will do if
it doesn't contain tuple IDs.

 Can you also pls explain me more about appoach to follow to use my
> algorithm as GiST opclass. I dont have much knowledge abt this also?
>

See user manual, and the examples in the server code and contrib.

- Heikki

Re: [HACKERS] Minmax indexes

2013-11-11 Thread Jeff Janes

On Mon, Nov 11, 2013 at 12:53 AM, Erik Rijkers  wrote:

> On Fri, November 8, 2013 21:11, Alvaro Herrera wrote:
> >
> > Here's a version 7 of the patch, which fixes these bugs and adds
> > opclasses for a bunch more types (timestamp, timestamptz, date, time,
> > timetz), courtesy of Martín Marqués.  It's also been rebased to apply
> > cleanly on top of today's master branch.
> >
> > I have also added a selectivity function, but I'm not positive that it's
> > very useful yet.
> >
> > [minmax-7.patch]
>
> The earlier errors are indeed fixed; now, I've been trying with the
> attached test case but I'm unable to find a query that
> improves with minmax index use.  (it gets used sometimes but speedup is
> negligable).
>


Your data set seems to be completely random.  I believe that minmax indices
would only be expected to be useful when the data is clustered.  Perhaps
you could try it on a table where it is populated something like
 i+random()/10*max_i.

Cheers,

Jeff

Re: [HACKERS] Add %z support to elog/ereport?

Andres Freund  writes:
> On 2013-11-11 11:18:22 -0500, Tom Lane wrote:
>> I think you'll find that %m is a totally different animal, because it
>> doesn't involve consuming an argument position.

> I was thinking of just replacing '%z' by '%l', '%ll' or '%' as needed
> and not expand it inplace. That should deal with keeping the argument
> position and such.
> But that won't easily work if somebody specifies flags like padding :/

[ reads manual ]  Oh, I see that actually z *is* a flag, so you'd be
talking about replacing it with a different flag, ie %zu -> %llu or
similar.  Yes, in principle that could work, but you'd have to be
prepared to cope with other flags, field width/precision, n$, etc,
appearing first.  Sounds a bit messy, and this code is probably a
hot spot, remembering what we found out about the cost of fooling
with log_line_prefix.

>> I'm less than sure that every version of gcc will recognize %z, either
>> ...

> It's been in recognized in 2.95 afaics, so I think we're good.

[ fires up old HPUX box ]  Nope:

$ cat test.c
#include 
#include 

int main(int argc, char**argv)
{
char buf[256];
size_t x = 0;

sprintf(buf, "%zu", (int)x);

return 0;
}
$ gcc -O2 -Wall test.c
test.c: In function `main':
test.c:9: warning: unknown conversion type character `z' in format
test.c:9: warning: too many arguments for format
$ gcc -v  
Reading specs from /usr/local/lib/gcc-lib/hppa2.0-hp-hpux10.20/2.95.3/specs
gcc version 2.95.3 20010315 (release)

We might be willing to toss 2.95 overboard by now, but we'd need to be
sure of exactly what the new minimum usable version is.

>> and what about the translation infrastructure?

> That I have no clue about yet.

Me either.  I do recall Peter saying that the infrastructure knows how to
verify conversion specs in translated strings, so it would have to be
aware of 'z' flags for this to work.

regards, tom lane

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

Re: [HACKERS] Add %z support to elog/ereport?

On 2013-11-11 17:33:53 +0100, Andres Freund wrote:
> On 2013-11-11 11:18:22 -0500, Tom Lane wrote:
> > Andres Freund  writes:
> > > I'd like to add support for the length modifier %z. Linux' manpages
> > > describes it as:
> > >  z  A  following  integer conversion corresponds to a size_t or 
> > > ssize_t argument.

> > and what about the translation infrastructure?
> 
> That I have no clue about yet.

gettext has support for it afaics, it's part of POSIX:
http://www.gnu.org/software/gettext/manual/gettext.html#c_002dformat
http://www.opengroup.org/onlinepubs/007904975/functions/fprintf.html

Greetings,

Andres Freund

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


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

Re: [HACKERS] Comment - uniqueness of relfilenode

2013-11-11 Thread Antonin Houska

On 11/10/2013 12:57 AM, Robert Haas wrote:
> On Thu, Nov 7, 2013 at 10:56 AM, Antonin Houska
>  wrote:
>> catalog/catalog.c:GetNewRelFileNode() and its calls indicate that the
>> following change makes sense:
>>
>>
>> diff --git a/src/include/storage/relfilenode.h
>> b/src/include/storage/relfilenode.h
>> index 75f897f..7190974 100644
>> --- a/src/include/storage/relfilenode.h
>> +++ b/src/include/storage/relfilenode.h
>> @@ -55,7 +55,7 @@ typedef enum ForkNumber
>>   * relNode identifies the specific relation.  relNode corresponds to
>>   * pg_class.relfilenode (NOT pg_class.oid, because we need to be able
>>   * to assign new physical files to relations in some situations).
>> - * Notice that relNode is only unique within a particular database.
>> + * Notice that relNode is only unique within a particular tablespace.
>>   *
>>   * Note: spcNode must be GLOBALTABLESPACE_OID if and only if dbNode is
>>   * zero.  We support shared relations only in the "global" tablespace.
>>
>>
>> // Antonin Houska (Tony)
> 
> Technically speaking, I think it's only guaranteed to be unique with a
> database-tablespace combination.  In other words, the same OID can be
> reused as a relfilenode if *either* of those two values differs.

You're right. I missed the fact that Postgres (unlike another DBMS that
I worked with) allows for tablespace to be shared across databases.

// Antonin Houska (Tony)



-- 
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] Fwd: Test of Algorithm || Indexing Scheme

2013-11-11 Thread Heikki Linnakangas


On 11.11.2013 14:12, Rohit Goyal wrote:

Hi,
Thanks for reply.
I actually want to make some changes in all operations on index like
insert, update, delete.
for example, i want store a new customized value for every key inserted in
b tree instead of storing Tuple Id as value.


That sounds pretty exotic, I can't imagine what good the index will do 
if it doesn't contain tuple IDs.



Can you also pls explain me more about appoach to follow to use my
algorithm as GiST opclass. I dont have much knowledge abt this also?


See user manual, and the examples in the server code and contrib.

- 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] Add %z support to elog/ereport?

On 2013-11-11 11:18:22 -0500, Tom Lane wrote:
> Andres Freund  writes:
> > I'd like to add support for the length modifier %z. Linux' manpages
> > describes it as:
> >  z  A  following  integer conversion corresponds to a size_t or ssize_t 
> > argument.
> 
> > Since gcc's printf format checks understand it, we can add support for
> > it similar to the way we added %m support.
> 
> I think you'll find that %m is a totally different animal, because it
> doesn't involve consuming an argument position.

I was thinking of just replacing '%z' by '%l', '%ll' or '%' as needed
and not expand it inplace. That should deal with keeping the argument
position and such.
But that won't easily work if somebody specifies flags like padding :/

> I'm less than sure that every version of gcc will recognize %z, either
> ...

It's been in recognized in 2.95 afaics, so I think we're good.

> and what about the translation infrastructure?

That I have no clue about yet.

Greetings,

Andres Freund

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


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

Re: [HACKERS] Add %z support to elog/ereport?

Andres Freund  writes:
> I'd like to add support for the length modifier %z. Linux' manpages
> describes it as:
>  z  A  following  integer conversion corresponds to a size_t or ssize_t 
> argument.

> Since gcc's printf format checks understand it, we can add support for
> it similar to the way we added %m support.

I think you'll find that %m is a totally different animal, because it
doesn't involve consuming an argument position.  I'm less than sure that
every version of gcc will recognize %z, either ... and what about the
translation infrastructure?

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] Execute query with EXCEPT, INTERSECT as anti-join, join?

"Colin 't Hart"  writes:
> Would these be difficult to build in?

Well, you'd have to worry about the ALL cases, as well as how to determine
whether you're actually getting a win (which would probably be rather
tough, really, as the choice would have to be made before we've fired up
any of the planner machinery that supports statistical estimation :-().
The code that plans this is in src/backend/optimizer/prep/prepunion.c.

regards, tom lane


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

[HACKERS] Add %z support to elog/ereport?

Hi,

I'd like to add support for the length modifier %z. Linux' manpages
describes it as:
 z  A  following  integer conversion corresponds to a size_t or ssize_t 
argument.

Since gcc's printf format checks understand it, we can add support for
it similar to the way we added %m support.

Currently we just deal with wanting to print size_t/Size values by
casting them to uint32, uint64 or similar, but that's a) annoying
because 64bit numbers require the annoying UINT64_FORMAT b) more and
more likely to be problematic when casting to 32bit numbers.

Does anybody see prolbems with that?

Greetings,

Andres Freund

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


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

Re: [HACKERS] TABLE not synonymous with SELECT * FROM?

2013-11-11 Thread David Johnston

Colin 't Hart wrote
> Methinks we should fix the documentation, something like:
> 
> The command
> 
> TABLE name
> 
> is equivalent to
> 
> SELECT * FROM name
> 
> It can be used as a top-level command or as a space-saving syntax
> variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT,
> and Locking clauses and set operations can be used with TABLE; the
> WHERE and ORDER BY clauses and any form of aggregation cannot be used.

The paragraph is unnecessary if the Synopsis section of the SELECT
documentation is updated to correctly reflect all the valid clauses that can
be attached to TABLE.  The current reading implies that you cannot attach
anything so when you said LIMIT worked I was surprised.

Also, testing seems to confirm that the allowance of LIMIT implies that
OFFSET is allowed as well.

If TABLE is allowed as a top-level command why doesn't it get its own page
in the SQL commands section?  It really doesn't matter - and honestly while
I've known about it I've never actually thought to use it in actual queries
because as soon as you want to do something special you have to switch it
out for SELECT * FROM anyway - but it does seem inconsistent.

David J.




--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/TABLE-not-synonymous-with-SELECT-FROM-tp5777695p533.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.


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

Re: [HACKERS] Another bug(?) turned up by the llvm optimization checker

2013-11-11 Thread Greg Stark

On Mon, Nov 11, 2013 at 4:00 AM, Tom Lane  wrote:

> In any case, the issue looks bigger than just addRangeTableEntry
> itself.  Do you want to write up a patch?
>

I was going to include it in the overflow patch but I'm now thinking I
should make it a separate commit to make sure the change in the contract
isn't buried in overflow check changes that are supposed to not change
functionality. I'll do that.

-- 
greg

Re: [HACKERS] Datatyp of a column

2013-11-11 Thread Ishaya Bhatt

Yes!! Thats exactly what I was looking for !! Thanks :)


On Mon, Nov 11, 2013 at 7:58 PM, Tom Lane  wrote:

> Ishaya Bhatt  writes:
> >  In the sorting code, I need to determine the datatype of my sort
> keys
> > and call some code conditionally based on the datatype. Is there any way
> to
> > determine the datatype of a column from the *backend* PostGreSQL code. is
> > the datatype of the column available in the query plan? Any help on this
> > would be very much appreciated.
>
> You really need to be more specific about where you need this information.
> The "sorting code" certainly knows what datatypes it's working with ---
> for example, in tuplesort.c there's a TupleDesc for the tuples passing
> through the sort, and the column types are available from the per-column
> atttypid fields of that.  But it's not clear if that's what you're talking
> about.
>
> regards, tom lane
>

Re: [HACKERS] TABLE not synonymous with SELECT * FROM?

On 11 November 2013 15:03, Tom Lane  wrote:
> "Colin 't Hart"  writes:
>> I would've thought it was implemented as a shortcut for "SELECT *
>> FROM" at the parse level (ie encounter "TABLE" and insert "SELECT *
>> FROM" into the parse tree and continue), but it seems there is more to
>> it.
>
> If you look at the PG grammar you'll see that "TABLE relation_expr"
> appears as one variant of simple_select, which means that you can attach
> WITH, ORDER BY, FOR UPDATE, or LIMIT to it.  The other things you mention
> are only possible in a clause that actually starts with SELECT.  AFAICS,
> this comports with the SQL standard's syntax specification (look at the
> difference between  and ).
> The comment for simple_select saith
>
>  * Note that sort clauses cannot be included at this level --- SQL requires
>  *  SELECT foo UNION SELECT bar ORDER BY baz
>  * to be parsed as
>  *  (SELECT foo UNION SELECT bar) ORDER BY baz
>  * not
>  *  SELECT foo UNION (SELECT bar ORDER BY baz)
>  * Likewise for WITH, FOR UPDATE and LIMIT.  Therefore, those clauses are
>  * described as part of the select_no_parens production, not simple_select.
>  * This does not limit functionality, because you can reintroduce these
>  * clauses inside parentheses.

Makes sense. I had been wondering about that order by stuff too.

Methinks we should fix the documentation, something like:

The command

TABLE name

is equivalent to

SELECT * FROM name

It can be used as a top-level command or as a space-saving syntax
variant in parts of complex queries. Only the WITH, ORDER BY, LIMIT,
and Locking clauses and set operations can be used with TABLE; the
WHERE and ORDER BY clauses and any form of aggregation cannot be used.


Cheers,

Colin


-- 
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] Execute query with EXCEPT, INTERSECT as anti-join, join?

On 11 November 2013 15:16, Tom Lane  wrote:
> "Colin 't Hart"  writes:
>> On 11 November 2013 14:34, Tom Lane  wrote:
>>> No, and it probably won't ever be, since the semantics aren't the same.
>>> EXCEPT/INTERSECT imply duplicate elimination.
>
>> Can't we just use DISTINCT for that?
>
> If you have to do a DISTINCT it's not clear to me that you're going to get
> much win.
>
> (The bigger picture here is that pretty much zero optimization effort has
> been spent on EXCEPT/INTERSECT, because they're just not used that much
> compared to other places where we could put that effort.)

I'm asking because I just encountered several cases where the
anti-join was *much* faster. In each case 's result was
relatively small compared to 's result or the "related rows"
from  were a much smaller set than the whole result of
. In these cases, when the executor new how the two halves of
the query were related -- and that's the crux here: by writing EXCEPT
the executor couldn't determine how the two halves of the query were
related -- the anti-join was about 1000 times faster.

I think it's similar to the NOT IN which most DBMSes solved about 20
years ago but before that everyone used to rewrite by hand as NOT
EXISTS: sometimes we want to write query as EXCEPT because it's
clearer but execute it as an anti-join with DISTINCT.

Would these be difficult to build in? While I know a lot about how
DBMS engines work I've not hacked at PG internals. I'd be more than
willing to look at it, but could use some pointers as to where to
start. In particular, does PG rewrite queries in any way? Is it
possible to simply "rewrite the query" and then pass to the optimizer
to see if it would result in a better plan?

if I can improve EXCEPT, the same could also be applied to INTERSECT
as a "DISTINCT join".

Thanks & regards,

Colin

-- 
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_dump and pg_dumpall in real life

2013-11-11 Thread Andrew Dunstan



On 11/11/2013 08:59 AM, Rafael Martinez wrote:


* It would be great to be able to tell pg_restore that user1 in the
dump will became user2 in the restored/cloned database. The same for
the name of the database.


A general ability to rename things would be good. In particular, 
restoring schema x into schema y or table x into table y would be very 
useful, especially if you need to be able to compare old with new.


Unfortunately, this would involve a fairly significant change in the 
design of pg_dump / pg_restore. The stored SQL is currently fairly 
opaque, and a renaming scheme would probably need to implement instead 
some sort of placeholder mechanism. That would mean a LOT of work.



cheers

andrew




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

Re: [HACKERS] Datatyp of a column

Ishaya Bhatt  writes:
>  In the sorting code, I need to determine the datatype of my sort keys
> and call some code conditionally based on the datatype. Is there any way to
> determine the datatype of a column from the *backend* PostGreSQL code. is
> the datatype of the column available in the query plan? Any help on this
> would be very much appreciated.

You really need to be more specific about where you need this information.
The "sorting code" certainly knows what datatypes it's working with ---
for example, in tuplesort.c there's a TupleDesc for the tuples passing
through the sort, and the column types are available from the per-column
atttypid fields of that.  But it's not clear if that's what you're talking
about.

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] pg_dump and pg_dumpall in real life

2013-11-11 Thread Stephen Frost

* Rafael Martinez (r.m.guerr...@usit.uio.no) wrote:
> * We need a pg_dump solution that can generate in one step all the
> necessary pieces of information needed when restoring or cloning a
> database. (schema, data, privileges, users and alter database/role data)

This sounds pretty reasonable and should be possible to implement- but
realize that, on the restore side, you might end up with multiple
attempts to create the same objects.  Consider a role that's depended
upon by objects in two databases- it would be included in the dump of
both of those databases and if you restored both of those into the same
cluster, one of the CREATE ROLE statements would fail.

I'd think this would also be a new pg_dump option along the lines of
'include global dependencies' or similar.  Reading the older threads, I
also agree that a '--create' version of pg_dump should include the
various SET commands for the database to be configured the same as the
one being dump'd.  The next part seems simple- let's get someone to do
it.. :)

> * It would be great to be able to tell pg_restore that user1 in the
> dump will became user2 in the restored/cloned database. The same for
> the name of the database.

This is a lot uglier, unfortunately.  We've seen this multiple times
before- there's not a good way to provide such a mapping as a command
line option.  There may also be issues with the dependency resolution..

> * For serious backup management of large and complicated databases,
> pg_dump with the custom output + pg_restore is the only feasible solution.

Sure; is there a question here?  I don't think that means we're going to
change the default, though there is a whole other thread on that
subject.

> What do you think about the subject?  Does it sound like a reasonable
> proposition? What do we need to implement some of these changes?

As for 'what we need', I'd think someone with the time and energy to
write the patch and work with the community to implement it..

Thanks,

Stephen

signature.asc
Description: Digital signature

Re: [HACKERS] Execute query with EXCEPT, INTERSECT as anti-join, join?

"Colin 't Hart"  writes:
> On 11 November 2013 14:34, Tom Lane  wrote:
>> No, and it probably won't ever be, since the semantics aren't the same.
>> EXCEPT/INTERSECT imply duplicate elimination.

> Can't we just use DISTINCT for that?

If you have to do a DISTINCT it's not clear to me that you're going to get
much win.

(The bigger picture here is that pretty much zero optimization effort has
been spent on EXCEPT/INTERSECT, because they're just not used that much
compared to other places where we could put that effort.)

regards, tom lane


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

[HACKERS] Datatyp of a column

2013-11-11 Thread Ishaya Bhatt

Hi,
 In the sorting code, I need to determine the datatype of my sort keys
and call some code conditionally based on the datatype. Is there any way to
determine the datatype of a column from the *backend* PostGreSQL code. is
the datatype of the column available in the query plan? Any help on this
would be very much appreciated.

Thanks,
Ishaya Bhatt.

Re: [HACKERS] TABLE not synonymous with SELECT * FROM?

"Colin 't Hart"  writes:
> I would've thought it was implemented as a shortcut for "SELECT *
> FROM" at the parse level (ie encounter "TABLE" and insert "SELECT *
> FROM" into the parse tree and continue), but it seems there is more to
> it.

If you look at the PG grammar you'll see that "TABLE relation_expr"
appears as one variant of simple_select, which means that you can attach
WITH, ORDER BY, FOR UPDATE, or LIMIT to it.  The other things you mention
are only possible in a clause that actually starts with SELECT.  AFAICS,
this comports with the SQL standard's syntax specification (look at the
difference between  and ).
The comment for simple_select saith

 * Note that sort clauses cannot be included at this level --- SQL requires
 *  SELECT foo UNION SELECT bar ORDER BY baz
 * to be parsed as
 *  (SELECT foo UNION SELECT bar) ORDER BY baz
 * not
 *  SELECT foo UNION (SELECT bar ORDER BY baz)
 * Likewise for WITH, FOR UPDATE and LIMIT.  Therefore, those clauses are
 * described as part of the select_no_parens production, not simple_select.
 * This does not limit functionality, because you can reintroduce these
 * clauses inside parentheses.

regards, tom lane

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

[HACKERS] pg_dump and pg_dumpall in real life

2013-11-11 Thread Rafael Martinez

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hello

After some discussions in #pgconfeu, this is an attempt to relaunch
the discussion about how pg_dump and pg_dumpall work and the
challenges they give us in real life.

We have got bitten sometimes because of their behavior and we can see
it is a difficult subject for new postgres users even if they have
long experience with others databases.

Logical backups are used for restores or cloning purposes. If as a
database administrator you want to be able to do one of these
procedures for a database, you need to do this in advance today:

1) Use pg_dump to dump schema, data and privileges of the database.
2) Use pg_dumpall -g to dump global objects
3) Use pg_dumpall -g to dump ALTER ROLE ... SET ... data
4) Use pg_dumpall to dump possible ALTER DATABASE ... SET ... data

For a cloning procedure we need the samme steps but usually and in
addition we have to change the name of the owner/database when
importing the dumps.

If you have just a few and not very complicated databases in your
cluster, these steps will not be very complicated although very
irritating.

Imagine you have several hundred databases in your cluster, with
several hundred users owning some objects and with grants in others.

Imagine you are cloning or restoring only one or a few of these
databases to another server. For 2), 3) and 4) you will have to parse
the output from pg_dumpall to get the few global objects, ALTER ROLE
and ALTER DATABASE data for the few databases you are restoring.

In addition, if you have used "GRANT .. ON .. TO ..." in your database
objects you will have to take care of this manually to find out who
has extra privileges in your objects, so you can also get the right
information from pg_dumpall -g.

You don't need a lot of imagination to understand what a mess this can
be when moving data around. Not to talk about the possibility of doing
something wrong in the process and not be a very robust solution.

After many years of using pg_dump/pg_dumpall, this is our experience,
our wishes and thoughts:

* We need a pg_dump solution that can generate in one step all the
necessary pieces of information needed when restoring or cloning a
database. (schema, data, privileges, users and alter database/role data)

* It would be great to be able to tell pg_restore that user1 in the
dump will became user2 in the restored/cloned database. The same for
the name of the database.

* For serious backup management of large and complicated databases,
pg_dump with the custom output + pg_restore is the only feasible solution.

What do you think about the subject?  Does it sound like a reasonable
proposition? What do we need to implement some of these changes?

Thanks in advance for your time.

Some background information:

Ref:
http://wiki.postgresql.org/wiki/Todo
http://www.postgresql.org/message-id/4864f001.50...@archonet.com
http://www.postgresql.org/message-id/11646.1272814...@sss.pgh.pa.us

regards,
- -- 
 Rafael Martinez Guerrero
 Center for Information Technology
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/
-BEGIN PGP SIGNATURE-
Version: GnuPG v2.0.14 (GNU/Linux)

iEYEARECAAYFAlKA4q4ACgkQBhuKQurGihSJJACglhZnjSTGFvzz6Rl0Vhrl3BrY
gssAni2l7kOQFxzr6IlDHAd0oMryDkT5
=Ti6V
-END PGP SIGNATURE-


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

Re: [HACKERS] Execute query with EXCEPT, INTERSECT as anti-join, join?

On 11 November 2013 14:34, Tom Lane  wrote:
> "Colin 't Hart"  writes:
>> I can't get Postgresql to execute a query with EXCEPT (or INTERSECT)
>> as an anti-join (or join).
>
>> Is this even possible?
>
> No, and it probably won't ever be, since the semantics aren't the same.
> EXCEPT/INTERSECT imply duplicate elimination.

Can't we just use DISTINCT for that?

Given a query

 EXCEPT 

isn't it always possible to rewrite this as

select distinct * from () q1 where not exists (select 1 from
() q2 where q1.col1 = q2.col1 and q1.col2 = c2.col2 and ...
and q1.colN = q2.colN)

?


Regards,

Colin


-- 
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] streaming header too small

2013-11-11 Thread Magnus Hagander

On Wed, Feb 20, 2013 at 5:02 PM, Heikki Linnakangas
 wrote:
> On 20.02.2013 17:53, Selena Deckelmann wrote:
>>
>> On Wed, Feb 20, 2013 at 6:23 AM, Magnus
>> Haganderwrote:
>>
>>> Selena, was this reasonably reproducible for you? Would it be possible to
>>> get a network trace of it to show of that's the kind of package coming
>>> across, or by hacking up pg_basebackup to print the exact position it was
>>> at when the problem occurred?
>>
>>
>> This is happening with a very busy 700 GB system, so I'm going to rule out
>> a network trace out for the moment. The error is occurring "sometime" in
>> the middle of the backup. Last time it was at least 30-40 minutes into a 2
>> hr backup.
>
>
> If you could pinpoint the WAL position where the error happens, that would
> already help somewhat. For starters, put pg_receivexlog to verbose mode, so
> that it will print a line after each WAL segment. If my theory is correct,
> the error should happen at xlogid boundaries, ie. just after finishing a WAL
> segment whose filename ends with "FE".

Your theory is correct, it happens at xlogid boundaries.

The missing information is that AFAICT it can only happen if
pg_basebackup is run against a slave, and never on the master.

I've applied a patch that just accepts this case, and ignores it.
Originally I had pg_basebackup write a warning in that case, but on
second thought I think that's just wrong - it will send out warning
messages in cases that are absolutely normal.

I'm not going to bother with a backend side patch, since this is
mostly harmless (it sends a single packet of an extra 25 bytes in
what's usually a large backup, so it doesn't matter), and it's all
gone in 9.3 anyway. And in 9.1 and earlier, the support isn't there.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Execute query with EXCEPT, INTERSECT as anti-join, join?

"Colin 't Hart"  writes:
> I can't get Postgresql to execute a query with EXCEPT (or INTERSECT)
> as an anti-join (or join).

> Is this even possible?

No, and it probably won't ever be, since the semantics aren't the same.
EXCEPT/INTERSECT imply duplicate elimination.

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] Fwd: Test of Algorithm || Indexing Scheme

Hi,
Thanks for reply.
I actually want to make some changes in all operations on index like
insert, update, delete.
for example, i want store a new customized value for every key inserted in
b tree instead of storing Tuple Id as value.

Can you also pls explain me more about appoach to follow to use my
algorithm as GiST opclass. I dont have much knowledge abt this also?

Regards,
Rohit Goyal

On Mon, Nov 11, 2013 at 12:31 PM, Heikki Linnakangas <
hlinnakan...@vmware.com> wrote:

> On 11.11.2013 13:19, Rohit Goyal wrote:
>
>> Hi All,
>>
>> I want to implement and test my indexing approach.
>> I would like to know which are the main files to look for b tree indexing
>> scheme modification.
>>
>> It would be great, if can share some helpful links which are needed to
>> understand how to modify and test an indexing scheme in postgresql.
>>
>
> You didn't give any details on what kind of changes you want to make, so
> hard to tell. The code for the btree access method is in
> src/backend/access/nbtree. See the README in that directory for the gory
> details.
>
> Also consider implementing your algorithm as a GiST opclass, instead of
> changing the internals.
>
> - Heikki
>

-- 
Regards,
Rohit Goyal

Re: [HACKERS] Fwd: Test of Algorithm || Indexing Scheme

2013-11-11 Thread Heikki Linnakangas


On 11.11.2013 13:19, Rohit Goyal wrote:

Hi All,

I want to implement and test my indexing approach.
I would like to know which are the main files to look for b tree indexing
scheme modification.

It would be great, if can share some helpful links which are needed to
understand how to modify and test an indexing scheme in postgresql.


You didn't give any details on what kind of changes you want to make, so 
hard to tell. The code for the btree access method is in 
src/backend/access/nbtree. See the README in that directory for the gory 
details.


Also consider implementing your algorithm as a GiST opclass, instead of 
changing the internals.


- Heikki


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

[HACKERS] Fwd: Test of Algorithm || Indexing Scheme

Hi All,

I want to implement and test my indexing approach.
I would like to know which are the main files to look for b tree indexing
scheme modification.

It would be great, if can share some helpful links which are needed to
understand how to modify and test an indexing scheme in postgresql.

Regards,
Rohit Goyal

Re: Custom Scan APIs (Re: [HACKERS] Custom Plan node)

2013-11-11 Thread Kohei KaiGai

Hi,

I tried to write up a wikipage to introduce how custom-scan works.

https://wiki.postgresql.org/wiki/CustomScanAPI

Any comments please.

2013/11/6 Kohei KaiGai :
> The attached patches provide a feature to implement custom scan node
> that allows extension to replace a part of plan tree with its own code
> instead of the built-in logic.
> In addition to the previous proposition, it enables us to integrate custom
> scan as a part of candidate paths to be chosen by optimizer.
> Here is two patches. The first one (pgsql-v9.4-custom-scan-apis) offers
> a set of API stuff and a simple demonstration module that implement
> regular table scan using inequality operator on ctid system column.
> The second one (pgsql-v9.4-custom-scan-remote-join) enhances
> postgres_fdw to support remote join capability.
>
> Below is an example to show how does custom-scan work.
>
> We usually run sequential scan even if clause has inequality operator
> that references ctid system column.
>
> postgres=# EXPLAIN SELECT ctid,* FROM t1 WHERE ctid > '(10,0)'::tid;
>QUERY PLAN
> 
>  Seq Scan on t1  (cost=0.00..209.00 rows= width=43)
>Filter: (ctid > '(10,0)'::tid)
> (2 rows)
>
> An extension that performs as custom-scan provider suggests
> an alternative path, and its cost was less than sequential scan,
> thus optimizer choose it.
>
> postgres=# LOAD 'ctidscan';
> LOAD
> postgres=# EXPLAIN SELECT ctid,* FROM t1 WHERE ctid > '(10,0)'::tid;
>   QUERY PLAN
> --
>  Custom Scan (ctidscan) on t1  (cost=0.00..100.00 rows= width=43)
>Filter: (ctid > '(10,0)'::tid)
> (2 rows)
>
> Of course, more cost effective plan will win if exists.
>
> postgres=# EXPLAIN SELECT ctid,* FROM t1 WHERE ctid > '(10,0)'::tid AND a = 
> 200;
> QUERY PLAN
> ---
>  Index Scan using t1_pkey on t1  (cost=0.29..8.30 rows=1 width=43)
>Index Cond: (a = 200)
>Filter: (ctid > '(10,0)'::tid)
> (3 rows)
>
> One other worthwhile example is remote-join enhancement on the
> postgres_fdw as follows. Both of ft1 and ft2 are foreign table being
> managed by same foreign server.
>
> postgres=# EXPLAIN (verbose) SELECT * FROM ft1 JOIN ft2 ON a = x
>   WHERE f_leak(b) AND y
> like '%aaa%';
>QUERY PLAN
> --
>  Custom Scan (postgres-fdw)  (cost=100.00..100.01 rows=0 width=72)
>Output: a, b, x, y
>Filter: f_leak(b)
>Remote SQL: SELECT r1.a, r1.b, r2.x, r2.y FROM (public.ft1 r1 JOIN
> public.ft2 r2 ON ((r1.a = r2.x))) WHERE ((r2.y ~~ '%aaa%'::text))
> (4 rows)
>
> ---
> How does it works
> ---
> This patch adds two hooks (for base and join relations) around allpaths.c
> and joinpaths.c. It allows extensions to add alternative paths to handle
> scanning on the base relation or join of two relations.
>
> Its callback routine can add CustomPath using add_path() to inform
> optimizer this alternative scan path. Every custom-scan provider is
> identified by its name being registered preliminary using the following
> function.
>
>   void register_custom_provider(const CustomProvider *provider);
>
> CustomProvider is a set of name string and function pointers of callbacks.
>
> Once CustomPath got chosen, create_scan_plan() construct a custom-
> scan plan and calls back extension to initialize the node.
> Rest of portions are similar to foreign scan, however, some of detailed
> portions are different. For example, foreign scan is assumed to return
> a tuple being formed according to table definition. On the other hand,
> custom-scan does not have such assumption, so extension needs to
> set tuple-descriptor on the scan tuple slot of ScanState structure by
> itself.
>
> In case of join, custom-scan performs as like a regular scan but it
> returns tuples being already joined on underlying relations.
> The patched postgres_fdw utilizes a hook at joinpaths.c to run
> remote join.
>
> 
> Issues
> 
> I'm not 100% certain whether arguments of add_join_path_hook is
> reasonable. I guess the first 7 arguments are minimum necessity.
> The mergeclause_list and semifactors might be useful if someone
> tries to implement its own mergejoin or semijoin. Also, I'm not
> good at usage of path parameterization, but the last two arguments
> are related to. Where is the best code to learn about its usage?
>
> +/* Hook for plugins to add custom join path, in addition to default ones */
> +typedef void (*add_join_path_hook_type)(PlannerInfo *root,
> +   RelOptInfo *joinrel,
> +

[HACKERS] Re: Exempting superuser from row-security isn't enough. Run predicates as DEFINER?

2013-11-11 Thread Kohei KaiGai

Hi Craig,

I'd like to vote the last options. It is a separate problem (or, might
be specification), I think.

According to the document of view,
http://www.postgresql.org/docs/devel/static/sql-createview.html

| Access to tables referenced in the view is determined by permissions of
| the view owner. In some cases, this can be used to provide secure but
| restricted access to the underlying tables. However, not all views are secure
| against tampering; see Section 38.5 for details. Functions called in the view
| are treated the same as if they had been called directly from the query using
| the view. Therefore the user of a view must have permissions to call all
| functions used by the view.

We checks permissions to the tables underlying a view with owner's
privilege of views, but permissions to the functions are checked by
the user who kicked the query.
I'm not certain what is the reason of this behavior except for implementation
reason, because table permission is checked on ExecCheckRTEPerms()
but function permission is checked on ExecEvalFunc(), thus its invocation
context is uncertain.

Anyway, it is a possible issue independent from existence of RLS feature.
So, we may need to consider an another solution, if we make a consensus
it is a problem to be tackled.
However, I think RLS is not a suitable solution towards this scenario.

Thanks,

2013/11/11 Craig Ringer :
> Hi all
>
> I'm thinking about a possible solution for one of the row-security
> issues - the ability of a malicious user to write a row-security policy
> containing a malicious predicate function, then trick another user into
> SELECTing from the table and running the function.
>
> What about running the row-security predicate subquery as the predicate
> definer - the user who SET the predicate - or the owner of the object
> the predicate applies to? How expensive are security context and user id
> changes - and is it even practical to do this within the context of a
> security barrier subquery?
>
> Oracle and Teradata get around this by making the assignment of row
> security constraints a highly privileged operation - table owners can't
> set their own. That's the other option IMO.
>
> We already have this as a known issue with VIEWs,
> CHECK constraints, etc, as shown below - so I'm tempted to
> hand-wave around it as a separate issue, and just say that you shouldn't
> access objects created by untrusted users.
>
> The problem is that CHECK constraints, VIEW access, etc doesn't affect
> pg_dump, it won't run view predicates or check constraint code. By
> contrast, pg_dump *does* read tables, so it needs to be exempted from
> row-security predicates defined by untrusted users. An exemption option
> is needed for performance anyway.
>
> Protecting pg_dump and the superuser alone aren't good enough, though.
> SuperSecretUser shouldn't have to fear SELECTing from a view written by
> user NewThisWeek.
>
> On a side note, pg_restore and psql running dump scripts *do* affect
> restores, which is kind of nasty.
>
> Here's a demo showing how to create a new superuser with a known
> password as a regular unprivileged user if you can trick the superuser
> into looking at one of your objects:
>
>
> CREATE USER user1;
>
> SET SESSION AUTHORIZATION user1;
>
> CREATE OR REPLACE FUNCTION haha() RETURNS boolean AS $$
> BEGIN
> RAISE NOTICE 'haha running as: %',current_user;
> CREATE USER haha PASSWORD 'haha' SUPERUSER;
> RETURN true;
> END;
> $$ LANGUAGE plpgsql;
>
> CREATE TABLE check_exploit ( a integer check (haha()) );
>
> CREATE VIEW view_exploit AS SELECT * FROM check_exploit WHERE haha();
>
> GRANT ALL ON check_exploit TO postgres;
> GRANT ALL ON view_exploit TO postgres;
>
> -- later, superuser comes along and looks at the table/view:
> SET SESSION AUTHORIZATION postgres;
>
>
> regress=# select * from view_exploit;
> NOTICE:  haha running as: postgres
>  a
> ---
>  1
> (1 row)
>
> regress=# \du haha
>List of roles
>  Role name | Attributes | Member of
> ---++---
>  haha  | Superuser  | {}
>
> regress=# DROP USER haha;
>
>
>
> or for an admin reason adds/ modifies a row in the table:
>
> regress=# INSERT INTO check_exploit VALUES (100);
> NOTICE:  haha running as: postgres
> INSERT 0 1
>
>
> This even works with SECURITY BARRIER views, since they do nothing to
> control the user ID the view predicate runs as.
>
> If the superuser dumps this database then restores the schema and data
> as two separate passes, "haha" will run via the check constraint in that
> case too. Ouch.
>
>
> So, we've got a few options:
>
> * Run the RS constraint subqueries as DEFINER or table owner (if
> possible and performant)
>
> * Restrict the ability to set RS predicates to superuser by default, and
> create a right to allow it to be delegated.
>
> * Call it a separate problem and deal with it later, since similar
> issues already apply to VIEW, CHECK, etc. Document that running pg_dump
> as a user without RS

Re: [HACKERS] Heavily modified big table bloat even in auto vacuum is running

2013-11-11 Thread Haribabu kommi

On 08 November 2013 18:35 Amit Kapila wrote:
> On Fri, Nov 8, 2013 at 10:56 AM, Haribabu kommi
>  wrote:
> > On 07 November 2013 09:42 Amit Kapila wrote:
> >> I am not sure whether the same calculation as done for
> new_rel_tuples
> >> works for new_dead_tuples, you can once check it.
> >
> > I didn't find any way to calculate new_dead_tuples like
> new_rel_tuples.
> > I will check it.
> >
> >> I am thinking that if we have to do estimation anyway, then wouldn't
> >> it be better to do the way Tom had initially suggested (Maybe we
> >> could have VACUUM copy the n_dead_tuples value as it exists when
> >> VACUUM starts, and then send that as the value to subtract when it's
> >> done?)
> >>
> >> I think the reason you gave that due to tuple visibility check the
> >> number of dead tuples calculated by above logic is not accurate is
> >> right but still it will make the value of dead tuples more
> >> appropriate than it's current value.
> >>
> >> You can check if there is a way to do estimation of dead tuples
> >> similar to new tuples, and it will be as solid as current logic of
> >> vac_estimate_reltuples(), then it's okay, otherwise use the other
> >> solution (using the value of n_dead_tuples at start of Vacuum) to
> >> solve the problem.
> >
> > The two approaches calculations are approximation values only.
> >
> > 1. Taking a copy of n_dead_tuples before VACUUM starts and then
> subtract it once it is done.
> >This approach doesn't include the tuples which are remains during
> the vacuum operation.
> 
>   Wouldn't next or future vacuum's will make the estimate more
> appropraite?

Possible only when nkeep counter value (tuples not cleaned) is very less value.
 
> > 2. nkeep counter contains the tuples which are still visible to other
> transactions.
> >This approach doesn't include tuples which are deleted on pages
> where vacuum operation is already finished.
> >
> > In my opinion the second approach gives the value nearer to the
> actual
> > value, because it includes some of the new dead tuples also. Please
> correct me if anything wrong in my analysis.
>I think main problem in nkeep logic is to come up with an estimation
> algorithm similar to live tuples.
> 
> By the way, do you have test case or can you try to write a test case
> which can show this problem and then after fix, you can verify if the
> problem is resolved.

The simulated index bloat problem can be generated using the attached script 
and sql.
With the fix of setting the dead tuples properly, the bloat is reduced and by 
changing the vacuum cost
Parameters the bloat is avoided.

The advantage with the fix is observed is the more number of times the auto 
vacuum is triggered on
The bloated table, as it satisfies the vacuum criteria because of proper dead 
tuples compared to the
original code.

Regards,
Hari babu.



Table_and_functions.sql
Description: Table_and_functions.sql


script.sh
Description: script.sh

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

[HACKERS] Execute query with EXCEPT, INTERSECT as anti-join, join?

Hi,

I can't get Postgresql to execute a query with EXCEPT (or INTERSECT)
as an anti-join (or join).

Is this even possible?

If not currently possible, is this something we would like to have?

Cheers,

Colin


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

2013-11-11 Thread Erik Rijkers

On Mon, November 11, 2013 09:53, Erik Rijkers wrote:
> On Fri, November 8, 2013 21:11, Alvaro Herrera wrote:
>>
>> Here's a version 7 of the patch, which fixes these bugs and adds
>> opclasses for a bunch more types (timestamp, timestamptz, date, time,
>> timetz), courtesy of Martín Marqués.  It's also been rebased to apply
>> cleanly on top of today's master branch.
>>
>> I have also added a selectivity function, but I'm not positive that it's
>> very useful yet.
>>
>> [minmax-7.patch]
>
> The earlier errors are indeed fixed; now, I've been trying with the attached 
> test case but I'm unable to find a query that
> improves with minmax index use.  (it gets used sometimes but speedup is 
> negligable).
>

Another issue (I think):

Attached is a program (and output as a .txt file) that gives the following 
(repeatable) error:

$ ./casanova_test.sh
\timing on
drop table if exists t1;
Time: 333.159 ms
create table t1 (i int);
Time: 155.827 ms
create index t1_i_idx on t1 using minmax(i);
Time: 204.031 ms
insert into t1 select generate_series(1, 2500);
Time: 126312.302 ms
analyze t1;
ERROR:  could not truncate file base/21324/26339_vm to 41 blocks: it's only 1 
blocks now
Time: 472.504 ms
[...]


Thanks,

Erik Rijkers


casanova_test.sh
Description: application/shellscript
$ ./casanova_test.sh 
\timing on
drop table if exists t1;
Time: 333.159 ms
create table t1 (i int);
Time: 155.827 ms
create index t1_i_idx on t1 using minmax(i);
Time: 204.031 ms
insert into t1 select generate_series(1, 2500);
Time: 126312.302 ms
analyze t1;
ERROR:  could not truncate file base/21324/26339_vm to 41 blocks: it's only 1 
blocks now
Time: 472.504 ms

\timing on
set enable_bitmapscan=1; explain analyze select i from t1 where i between 
1000 and 10001000;
Time: 0.508 ms
QUERY PLAN  
  
--
 Bitmap Heap Scan on t1  (cost=32.25..117786.77 rows=125001 width=4) (actual 
time=1640.520..4465.768 rows=1001 loops=1)
   Recheck Cond: ((i >= 1000) AND (i <= 10001000))
   Rows Removed by Index Recheck: 24998999
   ->  Bitmap Index Scan on t1_i_idx  (cost=0.00..1.00 rows=125001 width=0) 
(actual time=65.322..65.322 rows=291 loops=1)
 Index Cond: ((i >= 1000) AND (i <= 10001000))
 Total runtime: 4466.003 ms
(6 rows)

Time: 4468.943 ms
set enable_bitmapscan=0; explain analyze select i from t1 where i between 
1000 and 10001000;
Time: 0.146 ms
  QUERY PLAN
   
---
 Seq Scan on t1  (cost=0.00..485621.80 rows=125001 width=4) (actual 
time=1158.065..3332.925 rows=1001 loops=1)
   Filter: ((i >= 1000) AND (i <= 10001000))
   Rows Removed by Filter: 24998999
 Total runtime: .038 ms
(4 rows)
-- 
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] Get more from indices.

2013-11-11 Thread Kyotaro HORIGUCHI

Hello,

> Your patch fails the isolation test because of changed query plans:
> 
> http://pgci.eisentraut.org/jenkins/job/postgresql_commitfest_world/175/artifact/src/test/isolation/regression.diffs/*view*/

Thank you for pointing out. I wasn't aware of that..

# Because it is not launched from the top-level make check...

I'll count that in next pach.

-- 
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] Get more from indices.

2013-11-11 Thread Kyotaro HORIGUCHI

Thank you,

> In any case, it seems like a bad idea to me to conflate
> distinct-ness with ordering, so I don't like what you did to
> PathKeys.

Hmm, that sounds quite resonable in general. But the conflation
is already found in grouping_planner to some extent. The name
distinct_pathkey itself asserts that it is the ordering used for
distinct. And actually is used for sorting if hashed-distinct is
not selected.

Plus, these modifications in grouping_planner is required by the
patch for pathkeys.c to be effective.

I suppose the main cause of nastiness of the patch for
grouping_planner comes from the adheration of the usage of the
variable for path uniqueness with the existent code.

The additional members to Plan, say, pathkeys and ordered could
help the code to look less ugly by taking in the related code
currently appears nakedly in grouping_planner into make(or
generate)_plan() functions. Although the new attributes
somewhat look out of place..


> > However, if the index is unique, wouldn't
> > scanning the index produce data that actually satisfies the longer sort
> > key?  It doesn't matter what the values of c,d are if there are no
> > duplicates in the a,b columns.  So maybe as a separate patch, we could
> > look at claiming that a unique index satisfies the entire query_pathkeys
> > if it matches the first N columns of that.
> 
> That would be really spiffy.

# Putting aside the trueness of the assumption for unique-index
# and pathkeys.

The "expanded" sufficiency check can be archieved by involving
'unique-indexed' attribute for pathkeys_contained_in(),say
pathkeys_satisfies(pathkeys, pathkeys, is_uniq), but finally
could have no effect without some extent of assist in the process
in grouping_planner like my preveous patch to be in effect, I
believe.


I'll try to rewrite the path to be as following considering less
conflating lookings in grouping_planner.

 - is_unique and pathkeys is added to the type Path. (umm...)

 - create function like pathkeys_satisfies(check_pathkeys,
   pathkeys, isuniq) or path_ordered_by(pathkeys, path) as
   needed.

 - Plan will be set ordered and pathkeys derived from source path
   and its process and grouping_planner consults it to deceide
   whether to do sort (to hide the currently naked code).

 - Add check for NULLuble columns :-)

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] ECPG FETCH readahead

2013-11-11 Thread Boszormenyi Zoltan


2013-10-11 00:16 keltezéssel, Alvaro Herrera írta:

Boszormenyi Zoltan escribió:

2013-09-10 03:04 keltezéssel, Peter Eisentraut írta:

You need to update the dblink regression tests.

Done.

Dude, this is an humongous patch.  I was shocked by it initially, but on
further reading, I observed that it's only a huge patch which also does
some mechanical changes to test output.  I think it'd be better to split
the part that's responsible for the changed lines in test output
mentioning "ecpg_process_output".  That should be a reasonably small
patch which changes ecpg_execute slightly and adds the new function, is
followed by the enormous resulting mechanical changes in test output.
It should be possible to commit that relatively quickly.  Then there's
the rest of the patch, which would adds a huge pile of new code.

I think there are some very minor changes to backend code as well --
would it make sense to post that as a separate piece?


I had to rebase the patch against current (today morning's) GIT, since
there were a few changes against ECPG in the meantime.

The old contents of my GIT repository was removed so you need to
clone it fresh. https://github.com/zboszor/ecpg-readahead.git
I won't post the humongous patch again, since sending a 90KB
compressed file to everyone on the list is rude. You can pull
the commits individually from the above repository. For the same
reason, I won't add the DECLARE CURSOR command tag change
separately since this is also part of this big feature.

I have reordered some patches, like some independent bug fixes
against the ECPG parser and regression tests. The backend change
is also added early.

Best regards,
Zoltán Böszörményi

--
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/



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

[HACKERS] TABLE not synonymous with SELECT * FROM?