Re: [HACKERS] Visibility Groups

[daveg]

On Thu, Aug 07, 2008 at 01:30:27PM +0100, Gregory Stark wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> 
> > Currently, we calculate a single OldestXmin across all snapshots on the
> > assumption that any transaction might access any table.
> >
> > I propose creating "Visibility Groups" that *explicitly* limit the
> > ability of a transaction to access data outside its visibility group(s).
> > By default, visibility_groups would be NULL, implying potential access
> > to all tables.
> >
> > Once set, any attempt to lock an object outside of a transactions
> > defined visibility_groups will result in an error:
> >   ERROR attempt to lock table outside of visibility group(s): foo
> >   HINT you need to set a different value for visibility_groups
> > A transaction can only ever reduce or restrict its visibility_groups, it
> > cannot reset or add visibility groups.
> 
> Hm, so backing up a bit from the specific proposed interface, the key here is
> being able to explicitly mark which tables your transaction will need in the
> future?
> 
> Is it always just a handful of heavily updated tables that you want to
> protect? In that case we could have a lock type which means "I'll never need
> to lock this object". Then a session could issue "LOCK TABLE foo IN
> INACCESSIBLE MODE" or something like that. That requires people to hack up
> their pg_dump or replication script though which might be awkward.
> 
> Perhaps the way to do that would be to preemptively take locks on all the
> objects that you'll need, then have a command to indicate you won't need any
> further objects beyond those. 

+1

-dg
-- 
David Gould   [EMAIL PROTECTED]  510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.

-- 
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] autovacuum and TOAST tables

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Note that this patch allows a toast table to be vacuumed by the user:
> I don't have a problem with that, but if anyone thinks this is not a
> good idea, please speak up.

The permissions on pg_toast will prevent anyone but a superuser from
doing that anyway, so it's no big deal.

Possibly more interesting is what happens if someone drops the parent
table while VACUUM is working independently on the toast table.  Does
DROP take exclusive lock on a toast table?  Probably, but it needs
to be checked.  I think preventing that scenario was one reason why
the vacuuming was tied together way back when.

(The same goes for any other parent-table DDL action that would affect
the toast table; CLUSTER or TRUNCATE for instance.)

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] autovacuum and TOAST tables

[Alvaro Herrera]

Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > There's two things I'm not happy about in this patch:
> 
> > 1. it uses a List to keep the mapping of heap<->toast Oids.  This is
> > needed to be able to fetch the main rel's pg_autovacuum entry to process
> > the toast table.  This incurs in O(n^2) behavior.
> 
> Use a dynahash table instead?

Right, the attached patch does that.

Note that this patch allows a toast table to be vacuumed by the user:

alvherre=# vacuum pg_toast.pg_toast_39970;
VACUUM

I don't have a problem with that, but if anyone thinks this is not a
good idea, please speak up.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/vacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.375
diff -c -p -r1.375 vacuum.c
*** src/backend/commands/vacuum.c	5 Jun 2008 15:47:32 -	1.375
--- src/backend/commands/vacuum.c	8 Aug 2008 16:42:02 -
*** static BufferAccessStrategy vac_strategy
*** 213,220 
  static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
  			 const char *stmttype);
  static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! 	   bool for_wraparound);
  static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
  static void scan_heap(VRelStats *vacrelstats, Relation onerel,
  		  VacPageList vacuum_pages, VacPageList fraged_pages);
--- 213,220 
  static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
  			 const char *stmttype);
  static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast,
! 		   bool for_wraparound);
  static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
  static void scan_heap(VRelStats *vacrelstats, Relation onerel,
  		  VacPageList vacuum_pages, VacPageList fraged_pages);
*** static Size PageGetFreeSpaceWithFillFact
*** 268,273 
--- 268,276 
   * OID to be processed, and vacstmt->relation is ignored.  (The non-invalid
   * case is currently only used by autovacuum.)
   *
+  * do_toast is passed as FALSE by autovacuum, because it processes TOAST
+  * tables separately.
+  *
   * for_wraparound is used by autovacuum to let us know when it's forcing
   * a vacuum for wraparound, which should not be auto-cancelled.
   *
*** static Size PageGetFreeSpaceWithFillFact
*** 281,287 
   * at transaction commit.
   */
  void
! vacuum(VacuumStmt *vacstmt, Oid relid,
  	   BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
  {
  	const char *stmttype = vacstmt->vacuum ? "VACUUM" : "ANALYZE";
--- 284,290 
   * at transaction commit.
   */
  void
! vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
  	   BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
  {
  	const char *stmttype = vacstmt->vacuum ? "VACUUM" : "ANALYZE";
*** vacuum(VacuumStmt *vacstmt, Oid relid,
*** 433,439 
  			Oid			relid = lfirst_oid(cur);
  
  			if (vacstmt->vacuum)
! vacuum_rel(relid, vacstmt, RELKIND_RELATION, for_wraparound);
  
  			if (vacstmt->analyze)
  			{
--- 436,442 
  			Oid			relid = lfirst_oid(cur);
  
  			if (vacstmt->vacuum)
! vacuum_rel(relid, vacstmt, do_toast, for_wraparound);
  
  			if (vacstmt->analyze)
  			{
*** vac_truncate_clog(TransactionId frozenXI
*** 975,982 
   *		At entry and exit, we are not inside a transaction.
   */
  static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! 		   bool for_wraparound)
  {
  	LOCKMODE	lmode;
  	Relation	onerel;
--- 978,984 
   *		At entry and exit, we are not inside a transaction.
   */
  static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound)
  {
  	LOCKMODE	lmode;
  	Relation	onerel;
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1013,1020 
  		 * by autovacuum; it's used to avoid cancelling a vacuum that was
  		 * invoked in an emergency.
  		 *
! 		 * Note: this flag remains set until CommitTransaction or
! 		 * AbortTransaction.  We don't want to clear it until we reset
  		 * MyProc->xid/xmin, else OldestXmin might appear to go backwards,
  		 * which is probably Not Good.
  		 */
--- 1015,1022 
  		 * by autovacuum; it's used to avoid cancelling a vacuum that was
  		 * invoked in an emergency.
  		 *
! 		 * Note: these flags remain set until CommitTransaction or
! 		 * AbortTransaction.  We don't want to clear them until we reset
  		 * MyProc->xid/xmin, else OldestXmin might appear to go backwards,
  		 * which is probably Not Good.
  		 */
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1087,1096 
  	}
  
  	/*
! 	 * Check that it's a p

Re: [HACKERS] IN vs EXISTS equivalence

[Kevin Grittner]

>>> Tom Lane <[EMAIL PROTECTED]> wrote: 
 
> I believe that the optimizable cases for EXISTS are those where the
> EXISTS() is either at the top level of WHERE, or just underneath a
NOT,
 
The rest of the plan makes sense to me, but this part seems narrow. 
There's probably a good reason for that which is beyond my depth, but
attached is a view that is used for calculating statistics for a
database which is primarily used for "case management" purposes.  If
EXISTS could also be optimized in the contexts used there, it would be
great.
 
For context, this view references three other non-trivial views
(MatterHistSearch, MatterHistStage, & MatterHistStatus), and does
perform acceptably, so it's not a matter of complaining if it can't
work here, just providing a real-world example of other useful
contexts for EXISTS which might be worth covering if possible.
 
This view is used in a large number of queries, mostly either
selecting a single date with other selection criteria or counting rows
which match a set of matterNo values selected on complex criteria.
 
By the way, this view was totally unusable under 8.2.  Showing how it
worked under 8.3 was all it took to get them to expedite the upgrade. 
These views, possible because of improvements in 8.3, saved "countless
programmer days" (to quote one of the programmers).
 
-Kevin
CREATE VIEW "MatterDateStat" AS
SELECT
"S"."matterNo",
CAST("D"."date" AS "DateT") AS "date",
"S"."newStageCode" AS "stage",
CAST(COALESCE("O"."newStatusCode", 'OP') AS "MatterStatusCodeT") AS 
"status",
(
  EXISTS
  (
SELECT *
  FROM "MatterHistSearch" "MHS1"
  JOIN "MatterEventCode" "MEC1"
ON ("MEC1"."matterEventCode" = "MHS1"."matterEventCode")
  WHERE "MHS1"."matterNo" = "S"."matterNo"
AND "MHS1"."date" <= "D"."date"
AND "MEC1"."newMaintCode" = 'INA'
AND NOT EXISTS
(
  SELECT *
FROM "MatterHistSearch" "MHS2"
JOIN "MatterEventCode" "MEC2"
  ON ("MEC2"."matterEventCode" = "MHS2"."matterEventCode")
WHERE "MHS2"."matterNo" = "S"."matterNo"
  AND "MHS2"."date" <= "D"."date"
  AND ("MHS2"."date", "MHS2"."matterHistRowOrder") > 
("MHS1"."date", "MHS1"."matterHistRowOrder")
  AND "MEC2"."removeMaintCode" = 'INA'
)
  )
) AS "isOnHold",
"S"."parentMatter",
"S"."matterHistSeqNo",  -- NULL means that the stage is defaulting from the 
matter filing date.
"S"."areaOfLawCode",
"S"."county",
"S"."enteredDate",
"S"."filedDate",
"S"."grievInvestigator",
"S"."intakeInvestigator",
"S"."isSelfNotification",
"S"."litigationMatterNo",
"S"."matterType",
"S"."oldMatterNo",
"S"."reportMethodCode",
"S"."respondent",
"S"."sccaCaseNo",
"S"."takenBy",
"L"."isPublic",
"L"."matterDispoCode",
"L"."matterDispoDate"
  -- Oldest "filedDate" for a matter in the database is used.  (Older ones not 
likely to appear now.)
  FROM (SELECT DATE '1974-05-15' + generate_series(0, (CURRENT_DATE - DATE 
'1974-05-15')) AS "date") "D"
  JOIN "MatterHistStage" "S"
ON ( "S"."date" <= "D"."date"
 AND NOT EXISTS
 (
   SELECT * FROM "MatterHistStage" "S2"
 WHERE "S2"."matterNo" = "S"."matterNo"
   AND "S2"."date" <= "D"."date"
   AND ("S2"."date", "S2"."matterHistRowOrder") > ("S"."date", 
"S"."matterHistRowOrder")
 )
   )
  JOIN "Matter" "L"  -- Litigation matter, if present; otherwise the original 
matter.
ON ("L"."matterNo" = COALESCE("S"."litigationMatterNo", "S"."matterNo"))
  LEFT JOIN "MatterHistStatus" "O"
ON ( "O"."matterNo" = "S"."matterNo"
 AND "O"."date" <= "D"."date"
 AND NOT EXISTS
 (
   SELECT * FROM "MatterHistStatus" "O2"
 WHERE "O2"."matterNo" = "O"."matterNo"
   AND "O2"."date" <= "D"."date"
   AND ("O2"."date", "O2"."matterHistRowOrder") > ("O"."date", 
"O"."matterHistRowOrder")
 )
   )
  WHERE COALESCE("O"."newStatusCode", 'OP') <> 'CL'
;

-- 
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] Replay attack of query cancel

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> The problem was (third IIRC here :-P) in other clients, such as the JDBC
> driver (I think that one was checked specifically) which currently only
> accept the BackendKeyData message during startup. All drivers not based
> on libpq would have to be checked and potentially updated, but it's
> sitll a lot easier than DHing or so.

The other problem was getting the new cancel key from the postmaster to
the backend and thence to the client (hopefully in a timely manner),
recognizing that (a) we don't want the postmaster touching shared memory
very much, and certainly not engaging in any locking behavior; (b)
backends feel free to ignore SIGINT when they're not doing anything.

Certainly the prospect of a de facto protocol change is the bigger
problem, but there are nontrivial implementation issues in the server
too.

If we were going to make it a de jure protocol change (ie new version
number) instead of just hoping nobody notices the behavioral difference,
I'd be inclined to think about widening the cancel key, too.  32 bits
ain't that much randomness anymore.

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] Replay attack of query cancel

[Magnus Hagander]

Alvaro Herrera wrote:
> Heikki Linnakangas wrote:
> 
>> One idea for fixing this is to make cancellation keys disposable, and  
>> automatically issue a new one through the main connection when one is  
>> used, but that's not completely trivial, and requires a change in both  
>> the clients and the server. Another idea is to send the query cancel  
>> message only after SSL authentication, but that is impractical for libpq  
>> because we PQcancel needs to be callable from a signal handler.
> 
> I wonder if we can do something diffie-hellman'ish, where we have a
> parameter exchanged in the initial SSL'ed handshake, which is later used
> to generate new cancel keys each time the previous one is used.

Seems much more complex than needed.

IIRC, the protocol allows us (at least does not explicitly forbid) to
issue new cancel keys at any time. And libpq will, again IIRC, accept
them. So we could just change the server to issue a new cancel key
whenever it has used the previous one (since the new key will then be
sent over the encrypted channel, we're safe).

The problem was (third IIRC here :-P) in other clients, such as the JDBC
driver (I think that one was checked specifically) which currently only
accept the BackendKeyData message during startup. All drivers not based
on libpq would have to be checked and potentially updated, but it's
sitll a lot easier than DHing or so.

//Magnus

-- 
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] Replay attack of query cancel

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> I wonder if we can do something diffie-hellman'ish, where we have a
> parameter exchanged in the initial SSL'ed handshake, which is later used
> to generate new cancel keys each time the previous one is used.

Seems like the risk of getting out of sync would outweigh any benefits.
Lose one cancel message in the network, you have no hope of getting any
more accepted.

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] IN vs EXISTS equivalence

[Tom Lane]

"Kevin Grittner" <[EMAIL PROTECTED]> writes:
>>> I'm adding some NOT EXISTS examples to the thread for completeness
>>> of what someone might want to address while working on it.  For two
>>> queries which can easily be shown (to a human viewer, anyway) to
>>> return identical results, I see performance differences of over
>>> five orders of magnitude.

I've been looking at this a bit and have an action plan worked out.

I believe that the optimizable cases for EXISTS are those where the
EXISTS() is either at the top level of WHERE, or just underneath a NOT,
and the contained subselect:

* has no set operations (UNION etc), grouping, set-returning functions
in the SELECT list, LIMIT, or a few other funny cases

* references outer-level variables only in its WHERE clause.

Given these conditions, an EXISTS is equivalent to a standard semijoin
between the outer relations used in its WHERE clause and the sub-select
with the WHERE removed, where the join condition is just the WHERE
clause.  (A semijoin returns one copy of each LHS row for which there
exists at least one RHS row satisfying the join condition.)

Similarly, a NOT EXISTS is equivalent to a standard anti-semijoin.
(An anti-semijoin returns one copy of each LHS row for which there
exists no RHS row satisfying the join condition.)

So what I think we should do is implement JOIN_SEMI and JOIN_ANTI
as variant outer-join types in the planner and executor, and convert
EXISTS into those.  JOIN_SEMI would replace the existing JOIN_IN support.
(It's effectively the same thing so far as the executor is concerned,
but there are some places in the planner that assume an IN join condition
consists of one or more btree equality operators.  I'm envisioning folding
the current planner support for IN into the more general outer-join
planning infrastructure, and fixing it so that it doesn't assume the
join clause must be of that form.)

Explicit support for JOIN_ANTI is probably not strictly necessary:
we could represent it using the "LEFT JOIN WHERE rhs-variable IS NULL"
hack.  However this only works if the join's ON-condition can be proved
strict for at least one RHS variable, which is an assumption I'd rather
not require for optimizing EXISTS.  Also, the planner should be able to
make much better estimates of the size of an antijoin result if it has an
explicit concept that that's what's happening.  (The existing kluge in
nulltestsel() is not only ugly but has little hope of ever giving an
accurate estimate.)  So I'd prefer to go the other way: make the planner
recognize the IS NULL trick and remove the IS NULL clause in favor of
marking the LEFT JOIN as being really an antijoin.

As far as IN goes: IN at the top level of WHERE can still be optimized
to a semijoin under the same conditions as now.  NOT IN is a lot trickier,
for the same reason that typically trips up novices who try to use it:
if any row of the subselect produces a NULL comparison result, then it is
impossible for the NOT IN to result in TRUE, which means that it does not
function as a standard antijoin.  I thought about optimizing it only in
the case where we can prove that the subselect outputs and the compared-to
values are known NOT NULL (which in typical cases we could prove by
looking for NOT NULL constraints on those table columns).  The trouble
with this is that that's not a sufficient condition: you must also assume
that the comparison operator involved never yields NULL for non-null
inputs.  That might be okay for btree comparison functions but it's not a
very comfy assumption in general; we certainly haven't got any explicit
knowledge that any functions are guaranteed to act that way.  So this
case might be worth doing later but I'm not feeling excited about it.
We generally tell people to avoid NOT IN and I'm happy to keep on
saying that.

Comments?

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] Replay attack of query cancel

[Alvaro Herrera]

Heikki Linnakangas wrote:

> One idea for fixing this is to make cancellation keys disposable, and  
> automatically issue a new one through the main connection when one is  
> used, but that's not completely trivial, and requires a change in both  
> the clients and the server. Another idea is to send the query cancel  
> message only after SSL authentication, but that is impractical for libpq  
> because we PQcancel needs to be callable from a signal handler.

I wonder if we can do something diffie-hellman'ish, where we have a
parameter exchanged in the initial SSL'ed handshake, which is later used
to generate new cancel keys each time the previous one is used.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

[HACKERS] Replay attack of query cancel

[Heikki Linnakangas]

It occurred to me a while ago that our query cancel messages are sent 
unencrypted, even when SSL is otherwise used. That's not a big issue on 
its own, because the cancellation message only contains the backend PID 
and the cancellation key, but it does open us to a replay attack. After 
the first query in a connection has been cancelled, an eavesdropper can 
reuse the backend PID and cancellation key to cancel subsequent queries 
on the same connection.


We discussed this on the security list, and the consensus was that this 
isn't worth a quick fix and a security release, because

- it only affects applications that use query cancel, which is rare
- it only affects SSL encrypted connections (the point is moot 
non-encrypted connections, as you can just snatch the cancel key from 
the initial message)

- it only let's you cancel queries, IOW it's only a DOS attack.
- there's no simple fix.

However, it is something to keep in mind, and perhaps fix for the next 
release.


One idea for fixing this is to make cancellation keys disposable, and 
automatically issue a new one through the main connection when one is 
used, but that's not completely trivial, and requires a change in both 
the clients and the server. Another idea is to send the query cancel 
message only after SSL authentication, but that is impractical for libpq 
because we PQcancel needs to be callable from a signal handler.


--
  Heikki Linnakangas
  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] autovacuum and TOAST tables

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> There's two things I'm not happy about in this patch:

> 1. it uses a List to keep the mapping of heap<->toast Oids.  This is
> needed to be able to fetch the main rel's pg_autovacuum entry to process
> the toast table.  This incurs in O(n^2) behavior.

Use a dynahash table instead?

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] Avoiding Application Re-test

[chris]

[EMAIL PROTECTED] (Simon Riggs) writes:
> Every time we introduce a feature that changes output, we just put an if
> test in saying sql_compatibility = X, (the release we added feature).
>
> Straightforward, futureproof. Cool.

This is somewhat like the way that some shells try to emulate others;
for instance, zsh tries to emulate "sh" or "ksh" when invoked by those
names.

There are two problems with this:

   a) The conspicuous phrase, "try to emulate"

  Which leaves open several questions:

   - What if we can't?

   - What about when we don't want to (e.g. - because the new
 version has a new behaviour that we *DO* want)?

   - What if some semantic change takes place that we didn't
 realize should have been open to the "try to emulate"?  
 Such as, where there's an ordering change...

   b) It requires adding a new, not-previous-existant effort to
  classify changes and put in logic to allow controlling whether we
  use "legacy logic" or "new logic."

  That seems likely to get mighty messy to me, requiring
  "blabbering" control code throughout the code base.

I don't think we actually get the "future proofness" that you're
suggesting.  It would be nice if we could, but that seems unrealistic.
-- 
let name="cbbrowne" and tld="linuxdatabases.info" in String.concat "@" 
[name;tld];;
http://linuxfinances.info/info/spiritual.html
Rules of the Evil Overlord #204. "I will hire an entire squad of blind
guards.   Not only  is this  in  keeping with  my status  as an  equal
opportunity employer, but it will  come in handy when the hero becomes
invisible or douses my only light source."


-- 
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] Proposal of SE-PostgreSQL patches [try#2]

[Josh Berkus]

KaiGai Kohei wrote:

On the WiKi of CommitFest:Sep,
  http://wiki.postgresql.org/wiki/CommitFest:2008-09

The entry of SE-PostgreSQL points a message when I submitted older version
of our patch set. But the latest ones are listed on another message.

Please add a link to the following message for our convenience:
  http://archives.postgresql.org/pgsql-hackers/2008-07/msg01365.php


Hey, given the amount of work still to go on this, don't you think you 
should get a wiki account so that you can add comments yourself?


--Josh

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

[HACKERS] autovacuum and TOAST tables

[Alvaro Herrera]

Hi,

Here's a patch to make autovacuum process TOAST tables separately from
main tables.

The most important change is that when called from autovac, vacuum does
not process the TOAST table at all.  It will only do so when the stats
for the TOAST table say that it needs vacuuming.  (A user-invoked vacuum
still processes TOAST tables normally.)

Per previous discussion, the autovac code is now doing two passes over
pg_class.

There's two things I'm not happy about in this patch:

1. it uses a List to keep the mapping of heap<->toast Oids.  This is
needed to be able to fetch the main rel's pg_autovacuum entry to process
the toast table.  This incurs in O(n^2) behavior.

2. the "expected relkind" business is gone; it's not easy to pass the
correct relkind down from autovac, and at the same time have a
reasonable thing to pass down from user-invoked vacuum.  Right now what
the patch does is check that the rel to vacuum is either
RELKIND_RELATION or _TOASTVALUE.

(I admit that my unhappiness about the second is mild, though.)

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/commands/vacuum.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/commands/vacuum.c,v
retrieving revision 1.375
diff -c -p -r1.375 vacuum.c
*** src/backend/commands/vacuum.c	5 Jun 2008 15:47:32 -	1.375
--- src/backend/commands/vacuum.c	8 Aug 2008 16:42:02 -
*** static BufferAccessStrategy vac_strategy
*** 213,220 
  static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
  			 const char *stmttype);
  static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! 	   bool for_wraparound);
  static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
  static void scan_heap(VRelStats *vacrelstats, Relation onerel,
  		  VacPageList vacuum_pages, VacPageList fraged_pages);
--- 213,220 
  static List *get_rel_oids(Oid relid, const RangeVar *vacrel,
  			 const char *stmttype);
  static void vac_truncate_clog(TransactionId frozenXID);
! static void vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast,
! 		   bool for_wraparound);
  static void full_vacuum_rel(Relation onerel, VacuumStmt *vacstmt);
  static void scan_heap(VRelStats *vacrelstats, Relation onerel,
  		  VacPageList vacuum_pages, VacPageList fraged_pages);
*** static Size PageGetFreeSpaceWithFillFact
*** 268,273 
--- 268,276 
   * OID to be processed, and vacstmt->relation is ignored.  (The non-invalid
   * case is currently only used by autovacuum.)
   *
+  * do_toast is passed as FALSE by autovacuum, because it processes TOAST
+  * tables separately.
+  *
   * for_wraparound is used by autovacuum to let us know when it's forcing
   * a vacuum for wraparound, which should not be auto-cancelled.
   *
*** static Size PageGetFreeSpaceWithFillFact
*** 281,287 
   * at transaction commit.
   */
  void
! vacuum(VacuumStmt *vacstmt, Oid relid,
  	   BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
  {
  	const char *stmttype = vacstmt->vacuum ? "VACUUM" : "ANALYZE";
--- 284,290 
   * at transaction commit.
   */
  void
! vacuum(VacuumStmt *vacstmt, Oid relid, bool do_toast,
  	   BufferAccessStrategy bstrategy, bool for_wraparound, bool isTopLevel)
  {
  	const char *stmttype = vacstmt->vacuum ? "VACUUM" : "ANALYZE";
*** vacuum(VacuumStmt *vacstmt, Oid relid,
*** 433,439 
  			Oid			relid = lfirst_oid(cur);
  
  			if (vacstmt->vacuum)
! vacuum_rel(relid, vacstmt, RELKIND_RELATION, for_wraparound);
  
  			if (vacstmt->analyze)
  			{
--- 436,442 
  			Oid			relid = lfirst_oid(cur);
  
  			if (vacstmt->vacuum)
! vacuum_rel(relid, vacstmt, do_toast, for_wraparound);
  
  			if (vacstmt->analyze)
  			{
*** vac_truncate_clog(TransactionId frozenXI
*** 975,982 
   *		At entry and exit, we are not inside a transaction.
   */
  static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, char expected_relkind,
! 		   bool for_wraparound)
  {
  	LOCKMODE	lmode;
  	Relation	onerel;
--- 978,984 
   *		At entry and exit, we are not inside a transaction.
   */
  static void
! vacuum_rel(Oid relid, VacuumStmt *vacstmt, bool do_toast, bool for_wraparound)
  {
  	LOCKMODE	lmode;
  	Relation	onerel;
*** vacuum_rel(Oid relid, VacuumStmt *vacstm
*** 1013,1020 
  		 * by autovacuum; it's used to avoid cancelling a vacuum that was
  		 * invoked in an emergency.
  		 *
! 		 * Note: this flag remains set until CommitTransaction or
! 		 * AbortTransaction.  We don't want to clear it until we reset
  		 * MyProc->xid/xmin, else OldestXmin might appear to go backwards,
  		 * which is probably Not Good.
  		 */
--- 1015,1022 
  		 * by autovacuum; it's used to avoid cancelling 

Re: [HACKERS] CommitFest July Over

[Josh Berkus]

Markus,

Maybe it's just me, but I don't quite understand the concept of RRR. If 
I get some spare cycles to review patches, I certainly want to choose 
mysqlf which patch I'm going to review. Why is the CF Manager doing any 
assignment of patches?


This was actually by request of several reviewers, who said they could 
do something but didn't have a preference which patch to review.


--Josh


--
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] Verbosity of Function Return Type Checks

[Alvaro Herrera]

Volkan YAZICI wrote:

> Yesterday I needed to fiddle with PostgreSQL internals to be able to
> debug a PL/pgSQL procedure returning a set of records. I attached the
> patch I used to increase the verbosity of error messages related with
> function return type checks. I'll be appreciated if any developer could
> commit this patch (or a similar one) into the core.

I think this is a good idea, but the new error messages need more work.
Have a look at the message style guidelines please,
http://www.postgresql.org/docs/8.3/static/error-style-guide.html

Particularly I think you need to keep the original errmsg() and add the
new messages as errdetail().  (I notice that there's the slight problem
that the error messages are different for the different callers.)

Also, please use context diffs.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] Parsing of pg_hba.conf and authentication inconsistencies

[Stephen Frost]

Magnus,

* Magnus Hagander ([EMAIL PROTECTED]) wrote:
> Yeah. I think the question there is just - how likely is it that the
> same installation actually uses >1 authentication method. Personally, I
> think it's not very uncommon at all, but fact remains that as long as
> you only use one of them at a time, using a shared file doesn't matter.

We use multiple authentication types *alot*..  ident, md5, kerberos, and
gssapi are all currently in use on our systems.  ident for local unix
logins, md5 for 'role' accounts and software the doesn't understand
kerberos, kerberos/gssapi depending on the age of the client library
connecting.  Oh, and we use pam too..  We use some mappings now with
ident, which I'd expect to continue to do, and I've got definite plans
for mappings under Kerberos/GSSAPI once it's supported..

> > It wouldn't be very easy/clean to do that w/o breaking the existing
> > structure of pg_ident though, which makes me feel like using seperate
> > files is probably the way to go.
> 
> Yeah, thats my feeling as well. Now, can someone figure out a way to do
> that without parsing the file in the postmaster? (And if we do parse it,
> there's no point in not storing the parsed version, IMHO). And if not,
> the question it comes down to is which is most important - keeping the
> parsing away, or being able to do this ;-)

I don't have an answer wrt the parsing issue, but I definitely want to
be able to do this. :)

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Oprofile with postgresql

[Tom Lane]

"=?GB2312?B?tN7R0mNjdWl5eWFuQHNpbmEuY29t?=" <[EMAIL PROTECTED]> writes:
> Hi all:
>   Recently i do a test of postgresql. To get more information of the
> functions in PostgreSQL, i use

> opgprof and opannotate, which are two tools in Oprofile. But i can't work
> with the tools correctly.

I think you need to read the oprofile documentation a bit more and
possibly contact an oprofile mailing list for help.  This isn't a
Postgres-related question.

FWIW, I've used oprofile many times with postgres; it works as expected.
I'd be the first to agree it's got a steep learning curve though.

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] For what should pg_stop_backup wait?

[Simon Riggs]

On Fri, 2008-08-08 at 12:57 +0100, Simon Riggs wrote:

> > Yes, statement_timeout may help. But, I don't want to use it, because the
> > *successful* backup is canceled.
> > 
> > How about checking whether the stoppoint was archived by comparing with
> > the last WAL archived. The archiver process can tell the last WAL archived.
> > Or, we can calculate it from the status file.
> 
> I think its easier to test whether the stopxlogfilename still exists in
> pg_xlog. If not, we know it has been archived away. We can add that as
> an extra condition inside the loop.
> 
> So thinking we should test XLogArchiveCheckDone() for both
> stopxlogfilename and history file and then stat for the stop WAL file:

This seems better.

-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support
Index: src/backend/access/transam/xlog.c
===
RCS file: /home/sriggs/pg/REPOSITORY/pgsql/src/backend/access/transam/xlog.c,v
retrieving revision 1.316
diff -c -r1.316 xlog.c
*** src/backend/access/transam/xlog.c	13 Jul 2008 20:45:47 -	1.316
--- src/backend/access/transam/xlog.c	8 Aug 2008 13:56:40 -
***
*** 1165,1170 
--- 1165,1184 
  	/* Retry creation of the .ready file */
  	if (create_if_missing)
  		XLogArchiveNotify(xlog);
+ 	else
+ 	{
+ 		char xlogpath[MAXPGPATH];
+ 
+ 		snprintf(xlogpath, MAXPGPATH, XLOGDIR "/%s", xlog);
+ 
+ 		/*
+ 		 * Check to see if the WAL file has been removed by checkpoint, 
+ 		 * which implies it has already been archived, and explains why we
+ 		 * can't see a status file for it.
+ 		 */
+ 		if (stat(xlogpath, &stat_buf) != 0)
+ 			return true;
+ 	}
  
  	return false;
  }
***
*** 6721,6735 
  	CleanupBackupHistory();
  
  	/*
! 	 * Wait until the history file has been archived. We assume that the 
! 	 * alphabetic sorting property of the WAL files ensures the last WAL
! 	 * file is guaranteed archived by the time the history file is archived.
  	 *
  	 * We wait forever, since archive_command is supposed to work and
  	 * we assume the admin wanted his backup to work completely. If you 
  	 * don't wish to wait, you can SET statement_timeout = xx;
  	 *
! 	 * If the status file is missing, we assume that is because it was
  	 * set to .ready before we slept, then while asleep it has been set
  	 * to .done and then removed by a concurrent checkpoint.
  	 */
--- 6735,6748 
  	CleanupBackupHistory();
  
  	/*
! 	 * Wait until both the last WAL file filled during backup and the
! 	 * history file have been archived.
  	 *
  	 * We wait forever, since archive_command is supposed to work and
  	 * we assume the admin wanted his backup to work completely. If you 
  	 * don't wish to wait, you can SET statement_timeout = xx;
  	 *
! 	 * If the status files are missing, we assume that is because it was
  	 * set to .ready before we slept, then while asleep it has been set
  	 * to .done and then removed by a concurrent checkpoint.
  	 */
***
*** 6739,6745 
  	seconds_before_warning = 60;
  	waits = 0;
  
! 	while (!XLogArchiveCheckDone(histfilepath, false))
  	{
  		CHECK_FOR_INTERRUPTS();
  
--- 6752,6759 
  	seconds_before_warning = 60;
  	waits = 0;
  
! 	while (!XLogArchiveCheckDone(histfilepath, false) || 
! 		   !XLogArchiveCheckDone(stopxlogfilename, false))
  	{
  		CHECK_FOR_INTERRUPTS();
  

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

Re: [HACKERS] patch: Add columns via CREATE OR REPLACE VIEW

[Tom Lane]

Zeugswetter Andreas OSB sIT <[EMAIL PROTECTED]> writes:
>> If you accept the idea that column identity should be based on column
>> name, then the only two operations that are really necessary are
>> "CREATE OR REPLACE VIEW" and "ALTER VIEW RENAME COLUMN", and it is
>> 100% clear what the semantics of those operations should be.

> +1

It's nice, it's simple, and it's unimplementable.  At least not without
huge changes in the representation of stored views, which would likely
lead to failure to follow spec-required behavior in other ways.  Other
views are going to refer to the columns of this one by *number*, not
name, and it's not clear to me how you're going to preserve column
number identity with this approach.

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] Verbosity of Function Return Type Checks

[Volkan YAZICI]

Hi,

Yesterday I needed to fiddle with PostgreSQL internals to be able to
debug a PL/pgSQL procedure returning a set of records. I attached the
patch I used to increase the verbosity of error messages related with
function return type checks. I'll be appreciated if any developer could
commit this patch (or a similar one) into the core.


Regards.

Index: src/pl/plpgsql/src/pl_exec.c
===
RCS file: /projects/cvsroot/pgsql/src/pl/plpgsql/src/pl_exec.c,v
retrieving revision 1.216
diff -u -r1.216 pl_exec.c
--- src/pl/plpgsql/src/pl_exec.c	16 May 2008 18:34:51 -	1.216
+++ src/pl/plpgsql/src/pl_exec.c	8 Aug 2008 11:52:02 -
@@ -190,7 +190,7 @@
 	   Oid reqtype, int32 reqtypmod,
 	   bool isnull);
 static void exec_init_tuple_store(PLpgSQL_execstate *estate);
-static bool compatible_tupdesc(TupleDesc td1, TupleDesc td2);
+static void validate_tupdesc_compat(TupleDesc td1, TupleDesc td2);
 static void exec_set_found(PLpgSQL_execstate *estate, bool state);
 static void plpgsql_create_econtext(PLpgSQL_execstate *estate);
 static void free_var(PLpgSQL_var *var);
@@ -386,11 +386,12 @@
 			{
 case TYPEFUNC_COMPOSITE:
 	/* got the expected result rowtype, now check it */
-	if (estate.rettupdesc == NULL ||
-		!compatible_tupdesc(estate.rettupdesc, tupdesc))
+	if (!estate.rettupdesc)
 		ereport(ERROR,
 (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("returned record type does not match expected record type")));
+ errmsg("returned record type does not match "
+		"expected record type")));
+	validate_tupdesc_compat(tupdesc, estate.rettupdesc);
 	break;
 case TYPEFUNC_RECORD:
 
@@ -707,11 +708,8 @@
 		rettup = NULL;
 	else
 	{
-		if (!compatible_tupdesc(estate.rettupdesc,
-trigdata->tg_relation->rd_att))
-			ereport(ERROR,
-	(errcode(ERRCODE_DATATYPE_MISMATCH),
-	 errmsg("returned tuple structure does not match table of trigger event")));
+		validate_tupdesc_compat(trigdata->tg_relation->rd_att,
+estate.rettupdesc);
 		/* Copy tuple to upper executor memory */
 		rettup = SPI_copytuple((HeapTuple) DatumGetPointer(estate.retval));
 	}
@@ -2202,10 +2200,7 @@
 		   errmsg("record \"%s\" is not assigned yet",
   rec->refname),
 		   errdetail("The tuple structure of a not-yet-assigned record is indeterminate.")));
-	if (!compatible_tupdesc(tupdesc, rec->tupdesc))
-		ereport(ERROR,
-(errcode(ERRCODE_DATATYPE_MISMATCH),
-		errmsg("wrong record type supplied in RETURN NEXT")));
+	validate_tupdesc_compat(rec->tupdesc, tupdesc);
 	tuple = rec->tup;
 }
 break;
@@ -2311,10 +2306,7 @@
 		   stmt->params);
 	}
 
-	if (!compatible_tupdesc(estate->rettupdesc, portal->tupDesc))
-		ereport(ERROR,
-(errcode(ERRCODE_DATATYPE_MISMATCH),
-		  errmsg("structure of query does not match function result type")));
+	validate_tupdesc_compat(portal->tupDesc, estate->rettupdesc);
 
 	while (true)
 	{
@@ -5138,23 +5130,32 @@
 }
 
 /*
- * Check two tupledescs have matching number and types of attributes
+ * Validates compatibility of supplied TupleDesc's by checking # and type of
+ * available arguments.
  */
-static bool
-compatible_tupdesc(TupleDesc td1, TupleDesc td2)
+static void
+validate_tupdesc_compat(TupleDesc td1, TupleDesc td2)
 {
-	int			i;
+	int i;
 
 	if (td1->natts != td2->natts)
-		return false;
+		ereport(ERROR,
+(errcode(ERRCODE_DATATYPE_MISMATCH),
+ errmsg("Number of returned columns (%d) does not match "
+		"expected column count (%d).",
+	td1->natts, td2->natts)));
 
 	for (i = 0; i < td1->natts; i++)
-	{
 		if (td1->attrs[i]->atttypid != td2->attrs[i]->atttypid)
-			return false;
-	}
-
-	return true;
+			ereport(ERROR,
+	(errcode(ERRCODE_DATATYPE_MISMATCH),
+	 errmsg("Returned record type (%s) does not match "
+			"expected record type (%s) in column %d (%s).",
+			format_type_with_typemod(td1->attrs[i]->atttypid,
+ td1->attrs[i]->atttypmod),
+			format_type_with_typemod(td2->attrs[i]->atttypid,
+	 td2->attrs[i]->atttypmod),
+			(1+i), NameStr(td2->attrs[i]->attname;
 }
 
 /* --

-- 
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] For what should pg_stop_backup wait?

[Simon Riggs]

On Fri, 2008-08-08 at 11:47 +0900, Fujii Masao wrote:
> On Thu, Aug 7, 2008 at 11:34 PM, Simon Riggs <[EMAIL PROTECTED]> wrote:
> >

> In this situation, the history file (00010004.0020.backup)
> is behind the stoppoint (00010004) in the alphabetic order.
> So, pg_stop_backup should wait for both the stoppoint and the history
> file, I think.

OK, I see that now.

> 
> > !   while (!XLogArchiveCheckDone(stopxlogfilename, false))
> 
> If a concurrent checkpoint removes the status file before 
> XLogArchiveCheckDone,
> pg_stop_backup continues waiting forever. This is undesirable behavior.

I think it will only get removed by the second checkpoint, not the
first. So the risk of that happening seems almost certainly impossible.
But we'll put in a check just in case.

> Yes, statement_timeout may help. But, I don't want to use it, because the
> *successful* backup is canceled.
> 
> How about checking whether the stoppoint was archived by comparing with
> the last WAL archived. The archiver process can tell the last WAL archived.
> Or, we can calculate it from the status file.

I think its easier to test whether the stopxlogfilename still exists in
pg_xlog. If not, we know it has been archived away. We can add that as
an extra condition inside the loop.

So thinking we should test XLogArchiveCheckDone() for both
stopxlogfilename and history file and then stat for the stop WAL file:



BackupHistoryFileName(histfilepath, ThisTimeLineID, _logId, _logSeg,
  startpoint.xrecoff % 
XLogSegSize);

seconds_before_warning = 60;
waits = 0;

while (!XLogArchiveCheckDone(histfilepath, false) || 
   !XLogArchiveCheckDone(stopxlogfilename, false))
{
struct stat stat_buf;
charxlogpath[MAXPGPATH];

/*
 * Check to see if file has already been archived and WAL file
 * removed by a concurrent checkpoint
 */
snprintf(xlogpath, MAXPGPATH, XLOGDIR "/%s", stopxlogfilename);
if (XLogArchiveCheckDone(histfilepath, false) &&
stat(xlogpath, &stat_buf) != 0)
break;

CHECK_FOR_INTERRUPTS();

pg_usleep(100L);

if (++waits >= seconds_before_warning)
{
seconds_before_warning *= 2; /* This wraps in >10 
years... */
elog(WARNING, "pg_stop_backup() waiting for archive to 
complete " 
"(%d seconds delay)", 
waits);
}
}


-- 
 Simon Riggs   www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


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

[HACKERS] Oprofile with postgresql

[[EMAIL PROTECTED]]

Hi all:

  Recently i do a test of postgresql. To get more information of the
functions in PostgreSQL, i use

opgprof and opannotate, which are two tools in Oprofile. But i can't work
with the tools correctly.

PostgreSQL is compiled with -g option and the errors are like this:

 opgprof error: parse_filename() invalid filename:
/var/lib/oprofile/samples/current/{root}/var/lib/oprofile/samples/current/{root}/home/ubuntu/tpcc-uva/bin/tm/{dep}/{anon:[vdso]}/7208.0x7fff7a972000.0x7fff7a974000/CPU_CLK_UNHALTED.9.0.all.all.all/{dep}/{root}/var/lib/oprofile/samples/current/{root}/home/ubuntu/tpcc-uva/bin/tm/{dep}/{anon:[vdso]}/7208.0x7fff7a972000.0x7fff7a974000/CPU_CLK_UNHALTED.9.0.all.all.all/{cg}/{root}/usr/bin/oprofiled/CPU_CLK_UNHALTED.9.0.all.all.all

and opannotate

warning: [heap] (tgid:7302 range:0x8e7000-0xa18000) could not be found.
warning: [vdso] (tgid:7295 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7296 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7297 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7298 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7299 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7300 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7301 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
warning: [vdso] (tgid:7302 range:0x7fff3ddfe000-0x7fff3de0) could not be
found.
opannotate (warning): unable to open for reading: aset.c
opannotate (warning): unable to open for reading: heaptuple.c
opannotate (warning): unable to open for reading: bufmgr.c
opannotate (warning): unable to open for reading: execQual.c
opannotate (warning): unable to open for reading: list.c
opannotate (warning): unable to open for reading: nbtree.c
opannotate (warning): unable to open for reading: fmgr.c
opannotate (warning): unable to open for reading: catcache.c
opannotate (warning): unable to open for reading: nodeIndexscan.c
opannotate (warning): unable to open for reading: clauses.c
opannotate (warning): unable to open for reading: ri_triggers.c
opannotate (warning): unable to open for reading: lock.c
opannotate (warning): unable to open for reading: planner.c
opannotate (warning): unable to open for reading: selfuncs.c
opannotate (warning): unable to open for reading: postgres.c
opannotate (warning): unable to open for reading: trigger.c
...


if i want to use the two tools on PostgreSQL , what should i do?


Best wishes

  Yyan

Re: [HACKERS] patch: Add columns via CREATE OR REPLACE VIEW

[Zeugswetter Andreas OSB sIT]

> If you accept the idea that column identity should be based on column
> name, then the only two operations that are really necessary are
> "CREATE OR REPLACE VIEW" and "ALTER VIEW RENAME COLUMN", and it is
> 100% clear what the semantics of those operations should be.

+1

I think this would be an easily useable and understandable concept.
I also fully support Robert's reasoning in his next reply to Tom,
detailing why his patch's provided functionality is acceptable.

Andreas

PS: "alter view" in O does not change the base definition,
it only allows modifying view constraints.

-- 
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] Parsing of pg_hba.conf and authentication inconsistencies

[Magnus Hagander]

Stephen Frost wrote:
> Magnus,
> 
> * Magnus Hagander ([EMAIL PROTECTED]) wrote:
>> I thought of another issue with this. My "grand plan" includes being
>> able to do username mapping (per pg_ident.conf) for other authentication
>> methods than ident. Specifically this would be interesting for all
>> external methods, like gssapi/sspi/kerberos/ldap. I was originally
>> planning to allow each row in pg_hba to specify it's own pg_ident.conf
>> if necessary (so you could map LDAP and GSSAPI differently, for example,
>> or map two different kerberos realms differently). To be able to load
>> these, the postmaster would need to know about them, which means it'd
>> have to parse that data out anyway.
> 
> I certainly like the concept of having them be in seperate files.
> 
>> The other way to do that is to simply say that all external mapping will
>> use pg_ident.conf, and the only thing you can specify on a per-row basis
>> is "use map: yes or no". This decreases the flexibility, but would not
>> require the postmaster to do the parsing.
> 
> I don't think it makes sense to have multiple different auth types using
> the same mappings...  For Kerberos, as an example, we should support
> [EMAIL PROTECTED] as an option for the mapping, but that might not make sense
> for LDAP, which might have cn=User,ou=people,dc=example,dc=com, and
> neither of those really make sense for ident.  Mashing all of those
> together would make each auth type supporting the mapping have to search
> through the list trying to make sense of some mappings and throwing away
> others, just ugly all around..

Yeah. I think the question there is just - how likely is it that the
same installation actually uses >1 authentication method. Personally, I
think it's not very uncommon at all, but fact remains that as long as
you only use one of them at a time, using a shared file doesn't matter.


>> What do people think about these? I know Stephen for example really want
>> that feature so - would that restriction make it a lot less useful for you?
> 
> If we really wanted to keep it to a single *file*, then I think there
> should be a way to key rows in the pg_hba.conf to sets-of-rows in the
> mapping file.  eg: have an option of 'mapkey=xyz' in pg_hba, and then
> 'xyz' as the first column of the mapping file, with it being repeated
> across rows to form that mapping table.

Yuck. Honestly, that seems even uglier :-)


> It wouldn't be very easy/clean to do that w/o breaking the existing
> structure of pg_ident though, which makes me feel like using seperate
> files is probably the way to go.

Yeah, thats my feeling as well. Now, can someone figure out a way to do
that without parsing the file in the postmaster? (And if we do parse it,
there's no point in not storing the parsed version, IMHO). And if not,
the question it comes down to is which is most important - keeping the
parsing away, or being able to do this ;-)


//Magnus

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