Re: [HACKERS] Fixing PKs and Uniques in tablespaces

2004-07-23 Thread Peter Eisentraut
Christopher Kings-Lynne wrote:
> > Also, since I checked and it seems that our syntax for putting
> > tables an d indexes in tablespaces at creation time is identical to
> > oracle's, perhaps we should copy them on constraints as well.
>
> Since we're getting close to beta, can we have consensus on what I'm
> to do about this?

I think it's OK if  you can do the Oracle syntax (or some other 
established syntax) without reserving the key word TABLESPACE.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [HACKERS] psql listTables

2004-07-23 Thread Thomas F . O'Connell
I know, but I don't get too many opportunities to contribute... :)
Just figured I'd mention it.
-tfo
On Jul 23, 2004, at 4:06 PM, Tom Lane wrote:
"Thomas F.O'Connell" <[EMAIL PROTECTED]> writes:
I realize this is a trivial issue, but it seems like logic could be
added to the CASE statement to prevent irrelevant SELECT material from
being output.
Hardly seems worth the trouble ...
			regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


Re: [HACKERS] psql listTables

2004-07-23 Thread Tom Lane
"Thomas F.O'Connell" <[EMAIL PROTECTED]> writes:
> I realize this is a trivial issue, but it seems like logic could be 
> added to the CASE statement to prevent irrelevant SELECT material from 
> being output.

Hardly seems worth the trouble ...

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


Re: [HACKERS] ffunc called multiple for same value

2004-07-23 Thread Tom Lane
Ian Burrell <[EMAIL PROTECTED]> writes:
> We are doing things in the aggregates that make them troublesome when 
> called the ffunc is called multiple times.  The state structure uses a 
> lot of memory for intermediate work.  The memory needs to be freed as 
> soon as possible otherwise there is a danger of running of out memory. 

Possibly you should just force enable_hashagg off, if you are concerned
about memory usage.  ISTM that running multiple transvalue calculations
in parallel is a bad idea from the start, if you are feeling that tense
about the amount of memory that will be chewed up by just one.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


[HACKERS] psql listTables

2004-07-23 Thread Thomas F . O'Connell
In examining the output of psql -E to get some templates for some 
queries I'm developing, I noticed in describe.c that there is logic to 
inform the final IN clause that gets printed for relkind but no similar 
logic for the CASE clause.

Here's what I get from a \d in 7.4.1:
SELECT n.nspname as "Schema", c.relname as "Name",
CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as "Type",
u.usename as "Owner"
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
  AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Such that the IN clause for c.relkind seems to preclude two of the 
options ('i' and 's') in the CASE.

I realize this is a trivial issue, but it seems like logic could be 
added to the CASE statement to prevent irrelevant SELECT material from 
being output.

Couldn't the SELECT clause include if(showTables), etc.?
-tfo
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [HACKERS] ffunc called multiple for same value

2004-07-23 Thread Ian Burrell
Tom Lane wrote:
So I'm rather inclined to define this behavior as "not a bug".  The fact
that you're complaining seems to indicate that your ffunc scribbles on
its input, which is bad programming practice in any case.  Ordinarily
I would not think that an ffunc should have any problem with being
executed repeatedly on the same final transvalue.  (If you really want
to do things that way, maybe your code should take responsibility for
keeping a flag to execute just once, rather than pushing the cost onto
everybody.)
We are doing things in the aggregates that make them troublesome when 
called the ffunc is called multiple times.  The state structure uses a 
lot of memory for intermediate work.  The memory needs to be freed as 
soon as possible otherwise there is a danger of running of out memory. 
It is possible to store the resuts on the first ffunc call, free the 
intermediate state, return the results on later calls, and make sure the 
free only happens once.

The docs didn't make clear that calling ffunc multiple times could 
happens so we did not code to allow it.

 - Ian

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly


Re: [HACKERS] ffunc called multiple for same value

2004-07-23 Thread Mike Mascari
Tom Lane wrote:
So I'm rather inclined to define this behavior as "not a bug".  The fact
that you're complaining seems to indicate that your ffunc scribbles on
its input, which is bad programming practice in any case.  Ordinarily
I would not think that an ffunc should have any problem with being
executed repeatedly on the same final transvalue.  (If you really want
to do things that way, maybe your code should take responsibility for
keeping a flag to execute just once, rather than pushing the cost onto
everybody.)
Comments anyone?
As someone who makes use of C language aggregate functions, I agree 
with your analysis, so long as the fact that an ffunc may be invoked 
more than once is well documented, (i.e. an SGML  section 
might be nice.)

Mike Mascari

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [HACKERS] ffunc called multiple for same value

2004-07-23 Thread Tom Lane
Ian Burrell <[EMAIL PROTECTED]> writes:
> I posted a message a couple weeks ago abou having a problem with a 
> user-defined C language aggregate and the ffunc being called multiple 
> times with the same state.   I came up with a test case which shows the 
> problem with plpgsql functions.  It occurs with an aggregate in an inner 
> query, when a nested loop is used.

I looked into this and found that the unexpected behavior occurs only
when a HashAggregate plan is used.  If you force a GroupAggregate to be
used (set enable_hashagg = false), then you get one series of sfunc
calls and one ffunc call, per group per scan of the inner relation.
In the HashAgg code, the series of sfunc calls is executed only once per
group, with the final transvalue being stored in the hash table.  The
ffunc will be re-evaluated on each traversal of the hash table for
output --- which could be multiple times, if the grouped table is used
as the inside of a nestloop, as in this example.

I can imagine fixing this by having the HashAgg code replace the final
transvalue in the hash table with the ffunc result value.  It would not
be a whole lot of additional code, but it would make things noticeably
more complicated in what's already a rather complex bit of code (mainly
because transvalue and result could be different datatypes).  Probably
the worst objection is that with pass-by-reference result types, an
additional datumCopy step would be needed to stash the result in the
hash table (and there'd be an extra pfree, too).  That would slow things
down for everybody, with no gain unless the HashAgg result is in fact
read multiple times.

A different alternative which would be much lower-impact in terms of
code changes would be to change ExecReScanAgg() to always throw away
the hash table, even if it knows that the input data has not changed.
While this would avoid any time penalty for those not making use of
repeated scans, it would be a huge penalty for those that are, so it
hardly seems like an appealing choice either.

So I'm rather inclined to define this behavior as "not a bug".  The fact
that you're complaining seems to indicate that your ffunc scribbles on
its input, which is bad programming practice in any case.  Ordinarily
I would not think that an ffunc should have any problem with being
executed repeatedly on the same final transvalue.  (If you really want
to do things that way, maybe your code should take responsibility for
keeping a flag to execute just once, rather than pushing the cost onto
everybody.)

Comments anyone?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


[HACKERS] Can I determine the server name from PGresult?

2004-07-23 Thread Tony Reina
I have some libraries that serve as libpq wrappers. One function has
the PGresult structure pointer passed to it. I'd like to be able to
determine the host name from within the function, but I don't have the
PGconn pointer to use PQhost().

Is there any workaround to this? Can I somehow pass PGresult to
PQhost() or some similar function.

Way back before PG 7.0, I would have been able to link to PGconn from
within PGresult. Now it seems that the PGresult structure is
purposefully hidden from the programmer.

TIA,
-Tony

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [HACKERS] patch for allowing multiple -t options to pg_dump

2004-07-23 Thread Bruce Momjian

His patch has multiple -t options and -T.

---

Andreas Joseph Krogh wrote:
-- Start of PGP signed section.
> On Tuesday 20 July 2004 05:54, Bruce Momjian wrote:
> > Looks like someone else also just submitted the same patch, except with
> > a -T option to exclude tables. I will consider that version instead.
> 
> I can certainly see how that -T option is valuable, but I think multiple -t 
> options also make sense if you just want to dump 2 or 3 tables in a database 
> containing lots of tables. Why not include both?
> 
> -- 
> Andreas Joseph Krogh <[EMAIL PROTECTED]>
> Senior Software Developer / Manager
> gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
> +-+
> OfficeNet AS| - a tool should do one job, and do it well. |
> Hoffsveien 17   | |
> PO. Box 425 Sk?yen  | |
> 0213 Oslo   | |
> NORWAY  | |
> Phone : +47 22 13 01 00 | |
> Direct: +47 22 13 10 03 | |
> Mobile: +47 909  56 963 | |
> +-+
-- End of PGP section, PGP failed!

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [DOCS] [HACKERS] Tutorial

2004-07-23 Thread elein
Double postings are a PITB

- Forwarded message from elein <[EMAIL PROTECTED]> -

Date: Thu, 22 Jul 2004 21:31:37 -0700
From: elein <[EMAIL PROTECTED]>
To: Robert Treat <[EMAIL PROTECTED]>
Cc: Joe Conway <[EMAIL PROTECTED]>,
 elein <[EMAIL PROTECTED]>,
 David Fetter <[EMAIL PROTECTED]>,
 [EMAIL PROTECTED]
Subject: Re: [DOCS] [HACKERS] Tutorial
Mail-Followup-To: Robert Treat <[EMAIL PROTECTED]>,
Joe Conway <[EMAIL PROTECTED]>, David Fetter <[EMAIL PROTECTED]>,
[EMAIL PROTECTED]
In-Reply-To: <[EMAIL PROTECTED]>
User-Agent: Mutt/1.3.22.1i

Postgresql is not simply a relational database. It is an
OBJECT relational database.  It was designed to be so from
the start.  To pretend it was designed otherwise is to
deny its design and heritage and the original intent of
the the project.  c.f. the postgres papers, stonebraker, et.al.

And like tom said, "who said inheritance is not relational."
It need not break codds rules.

--elein



On Thu, Jul 22, 2004 at 10:40:45PM -0400, Robert Treat wrote:
> On Thursday 22 July 2004 21:07, Joe Conway wrote:
> > elein wrote:
> > > I like inhertitance, but believe that the usefulness
> > > of our implementation is limited and so the documentation
> > > should focus on other areas.
> >
> > +1
> >
> 
> +1/2 (Since I don't like inheritence)
> 
> IMHO we ought to try to keep the _tutorial_ free of things that are generally 
> considered against relational design.  If we must keep them, move them into 
> thier own section and lable them accordingly. 
> 
> Robert Treat
> --- 
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster

- End forwarded message -

---(end of broadcast)---
TIP 8: explain analyze is your friend


Re: [HACKERS] patch for allowing multiple -t options to pg_dump

2004-07-23 Thread Andreas Joseph Krogh
On Tuesday 20 July 2004 05:54, Bruce Momjian wrote:
> Looks like someone else also just submitted the same patch, except with
> a -T option to exclude tables. I will consider that version instead.

I can certainly see how that -T option is valuable, but I think multiple -t 
options also make sense if you just want to dump 2 or 3 tables in a database 
containing lots of tables. Why not include both?

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| - a tool should do one job, and do it well. |
Hoffsveien 17   | |
PO. Box 425 Skøyen  | |
0213 Oslo   | |
NORWAY  | |
Phone : +47 22 13 01 00 | |
Direct: +47 22 13 10 03 | |
Mobile: +47 909  56 963 | |
+-+


pgp9kQXxPKxFN.pgp
Description: PGP signature


Re: [HACKERS] Wrong index choosen?

2004-07-23 Thread Tom Lane
Dennis Bjorklund <[EMAIL PROTECTED]> writes:
> In this plan it estimates to get 481 but it got 22477. So the estimation 
> was very wrong. You can increase the statistics tarhet on the login_time 
> and it will probably be better (after the next analyze).

Given the nature of the data (login times), I'd imagine that the problem
is simply that he hasn't analyzed recently enough.  A bump in stats
target may not be needed, but he's going to have to re-analyze that
column often if he wants this sort of query to be estimated accurately,
because the fraction of entries later than a given time T is *always*
going to be changing.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings


Re: [HACKERS] make LockRelation use top transaction ID

2004-07-23 Thread Tom Lane
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> I just figured that if we let LockRelation use GetCurrentTransactionId()
> then the wrong thing happens if we let large objects survive
> subtransaction commit/abort.

> So I have changed it to use GetTopTransactionId() instead.  Is that OK
> with everybody?

No, at least not if you made that a global change.  Doing it that way
will mean that a failed subtransaction will not release its locks, no?

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org


Re: [HACKERS] Wrong index choosen?

2004-07-23 Thread Dennis Bjorklund
On Fri, 23 Jul 2004, Gaetano Mendola wrote:

> empdb=# explain analyze select * from v_past_connections where login_time > 
> '2004-07-21';
>QUERY PLAN
> --
>   Index Scan using idx_user_logs_login_time on user_logs  (cost=0.00..12.90 rows=481 
> width=28) (actual time=7.338..661.300 rows=22477 loops=1)
> Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone)
>   Total runtime: 676.472 ms
> (3 rows)

In this plan it estimates to get 481 but it got 22477. So the estimation 
was very wrong. You can increase the statistics tarhet on the login_time 
and it will probably be better (after the next analyze).

> why then the planner choose to do an index scan using the filter that
> retrieve a bigger ammount of rows ? A bug ?

Because it has to decide on the plan before it knows exactly what the 
result will be. As seen above, the estimation was wrong and thus the plan 
was not as good as it could have been.

In this case you probably also want to create a combined index on both
columns:

CREATE INDEX foo ON user_log (id_user, login_time);

ps. This letter belonged to pgsql-performance and not pgsql-hackers.

-- 
/Dennis Björklund


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [HACKERS] Fixing PKs and Uniques in tablespaces

2004-07-23 Thread Zeugswetter Andreas SB SD

> > Also, since I checked and it seems that our syntax for putting tables an 
> > d indexes in tablespaces at creation time is identical to oracle's, 
> > perhaps we should copy them on constraints as well.
> 
> Since we're getting close to beta, can we have consensus on what I'm to 
> do about this?

The Oracle 10g documentation has: USING INDEX TABLESPACE blabla
none of the words are optional.

Andreas

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


[HACKERS] Wrong index choosen?

2004-07-23 Thread Gaetano Mendola
I hall
I have a query in this form:
empdb=# explain analyze select * from v_past_connections where id_user = 26195 and 
login_time > '2004-07-21';
   QUERY PLAN
-
 Index Scan using idx_user_logs_login_time on user_logs  (cost=0.00..14.10 rows=1 
width=28) (actual time=66.890..198.998 rows=5 loops=1)
   Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone)
   Filter: (id_user = 26195)
 Total runtime: 199.083 ms
(4 rows)
as you see the index on the time stamp column is used
The table have indexes on both columns:
empdb=# explain analyze select * from v_past_connections where login_time > 
'2004-07-21';
  QUERY PLAN
--
 Index Scan using idx_user_logs_login_time on user_logs  (cost=0.00..12.90 rows=481 
width=28) (actual time=7.338..661.300 rows=22477 loops=1)
   Index Cond: (login_time > '2004-07-21 00:00:00+02'::timestamp with time zone)
 Total runtime: 676.472 ms
(3 rows)
empdb=# explain analyze select * from v_past_connections where id_user = 26195;
  QUERY PLAN
---
 Index Scan using idx_user_user_logs on user_logs  (cost=0.00..252.47 rows=320 
width=28) (actual time=4.420..100.122 rows=221 loops=1)
   Index Cond: (id_user = 26195)
 Total runtime: 100.348 ms
(3 rows)
The rows filtered out with both condictions are two order of magnitude differents,
also the extimated rows are close to real numbers:
empdb=# select count(*) from v_past_connections where id_user = 26195;
 count
---
   221
(1 row)
empdb=# select count(*) from v_past_connections where login_time > '2004-07-21';
 count
---
 22441
(1 row)
why then the planner choose to do an index scan using the filter that retrieve a 
bigger ammount of rows ? A bug ?


Regards
Gaetano Mendola







---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html


Re: [HACKERS] Fixing PKs and Uniques in tablespaces

2004-07-23 Thread Christopher Kings-Lynne
We are already in a features freeze period, or not ?
This isn't a feature, it's a bug...
Chris
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html


Re: [HACKERS] Fixing PKs and Uniques in tablespaces

2004-07-23 Thread Gaetano Mendola
Christopher Kings-Lynne wrote:
I never really considered oracle's implementation of tablespaces when I
worked on tablespaces. The database default tablespace seems similar to
Oracle's SYSTEM tablespace. I'm not sure if they use a global tablespace
like we do.

My point was that Oracle has added a tablespace clause to the 
constraint clause, so we can too!

Also, since I checked and it seems that our syntax for putting tables 
an d indexes in tablespaces at creation time is identical to oracle's, 
perhaps we should copy them on constraints as well.

Since we're getting close to beta, can we have consensus on what I'm to 
do about this?
We are already in a features freeze period, or not ?
Regards
Gaetano Mendola


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster


[HACKERS] make LockRelation use top transaction ID

2004-07-23 Thread Alvaro Herrera
Hackers,

I just figured that if we let LockRelation use GetCurrentTransactionId()
then the wrong thing happens if we let large objects survive
subtransaction commit/abort.  The problem is that when closing a large
object at main transaction commit, which was opened inside a
subtransaction, the code tries to UnlockRelation(pg_largeobject), and
use the main transaction Xid (instead of the subtransaction Xid that it
used to LockRelation()).

So I have changed it to use GetTopTransactionId() instead.  Is that OK
with everybody?

-- 
Alvaro Herrera ()
Maybe there's lots of data loss but the records of data loss are also lost.
(Lincoln Yeoh)


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org