Re: [GENERAL] Transaction size

[Alban Hertroys]

Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
Is it possible that there is some limitation to the number of statements 
in a single transaction?
2^32, and if you'd exceeded it, you'd get a very specific error message
saying so.
Well, that's a relief. At least it means I'm not running into some limit 
of my favourite database.

As they're inserts, and therefore not even touching the same data, I'm 
quite certain it's not some kind of row locking issue (does that even 
happen at all with MVCC?).
I'm not.  In particular this could be a foreign key locking issue ---
does the target table have foreign keys, and if so could inserts from
different transactions be referencing the same master row?
It does have a reference to a table with statusses, but those are rather 
static. I suppose an integrity check is comparable to doing a select 
with respect to locking strategies? (Meaning that it wouldn't be the 
cause of my problem).

Regards,
Alban Hertroys.
---(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

[GENERAL] handing created and updated fields

[Jim C. Nasby]

I think I saw something posted about this recently, but I can't find it
in the archives now. :(

I want to have created and updated fields in a table that are kept
up-to-date by the database and can't be changed accidentally. I think
this can be done with rules, but I'm not sure of the best way to do it.
Basically:

ON INSERT: force created and updated to be current_timestamp
ON UPDATE: deny updated created. force updated to be set to
current_timestamp

I first thought of doing an ON INSERT INSTEAD rule that would ignore
NEW.created and NEW.updated, but it seems inconvenient to have to change
the rule every time the table definition, and I'm not sure if this would
properly handle the SERIAL that I have defined (the rule would need to
include the serial in the insert, but then would the default work?). So
now I'm thinking of doing an ON INSERT INSTEAD UPDATE SET created =
current_timestamp WHERE id = NEW.id, though again I'm not sure if the
serial field (id) would be handled properly.

Does anyone have an example of the best way to handle this scenario?
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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

[GENERAL] ORDER BY in UNION query

[Antony Paul]

Hi,
I need to use ORDER BY clause in a UNION query and the Order BY
columns are not included in the SELECT statement. I tried like this

(select  from a) UNION (select . from b) order by a.ename;

It says that 
ERROR:  Attribute "ename" not found

How to do this.

rgds
Antony Paul

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] handing created and updated fields

[Martijn van Oosterhout]

On Mon, Jan 10, 2005 at 05:28:47AM -0600, Jim C. Nasby wrote:
> I think I saw something posted about this recently, but I can't find it
> in the archives now. :(
> 
> I want to have created and updated fields in a table that are kept
> up-to-date by the database and can't be changed accidentally. I think
> this can be done with rules, but I'm not sure of the best way to do it.
> Basically:
> 
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp

Nope, you want triggers. I don't remember the syntax, but the basic
structure would be...

ON INSERT DO TRIGGER set_timestamp
ON UPDATE DO TRIGGER update_timestamp

set_timestamp()
  NEW.created = now()
  NEW.updated = now()

update_timestamp()
  if OLD.created <> NEW.created then ERROR
  NEW.updated = now()

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.


pgpvZUvBLFzK2.pgp
Description: PGP signature

Re: [GENERAL] PostgreSQL 8.0.0 Release Candidate 4

[Mark Rae]

On Fri, Jan 07, 2005 at 12:04:00PM -0400, Marc G. Fournier wrote:
> A current list of *known* supported platforms can be found at:
>   http://developer.postgresql.org/supported-platforms.html
> We're always looking to improve that list, so we encourage anyone that is 
> running a platform not listed to please report on any success or failures 
> with Release Candidate 4.

I can confirm the following two architectures successfully
completed the regression tests, running
$ gmake MAX_CONNECTIONS=10 check

Machine:   SGI Altix 350
Processor: Itanium2 
OS:SGI ProPack 3SP1 for Linux, Build 301r3-0407280007
Compiler:  gcc 3.2.3

Machine:   HP/Compaq ES45
Processor: Alpha ev68
OS:Compaq Tru64 UNIX V5.1B (Rev. 2650)
Compiler:  Compaq C v6.5-011


-Mark

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

Re: [GENERAL] ORDER BY in UNION query

[Richard Huxton]

Antony Paul wrote:
Hi,
I need to use ORDER BY clause in a UNION query and the Order BY
columns are not included in the SELECT statement. I tried like this
(select  from a) UNION (select . from b) order by a.ename;
It says that 
ERROR:  Attribute "ename" not found

How to do this.
The "order by" is applying to the results of the union, not one of the 
sub-selects. If you want to sort by a value, you'll need to include it 
in the results list.

--
  Richard Huxton
  Archonet Ltd
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] ORDER BY in UNION query

[John Sidney-Woollett]

Try
select a.col1 as ename from a
union
select b.othercolumn as ename from b
order by ename
Give the columns you want to order on the same name using the "as XXX" 
syntax, and remove the "a." prefix from the order statement.

John Sidney-Woollett
Antony Paul wrote:
Hi,
I need to use ORDER BY clause in a UNION query and the Order BY
columns are not included in the SELECT statement. I tried like this
(select  from a) UNION (select . from b) order by a.ename;
It says that 
ERROR:  Attribute "ename" not found

How to do this.
rgds
Antony Paul
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] Transaction size

[Tom Lane]

Alban Hertroys <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Alban Hertroys <[EMAIL PROTECTED]> writes:
>>> As they're inserts, and therefore not even touching the same data, I'm 
>>> quite certain it's not some kind of row locking issue (does that even 
>>> happen at all with MVCC?).
>> 
>> I'm not.  In particular this could be a foreign key locking issue ---
>> does the target table have foreign keys, and if so could inserts from
>> different transactions be referencing the same master row?

> It does have a reference to a table with statusses, but those are rather 
> static. I suppose an integrity check is comparable to doing a select 
> with respect to locking strategies? (Meaning that it wouldn't be the 
> cause of my problem).

No, unfortunately it's more like a SELECT FOR UPDATE and it does take a
lock on the referenced row (with an eye to ensuring that the referenced
row can't go away before the new referencing row is committed).
I suspect this is indeed the cause of your problem.

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: [GENERAL] ORDER BY in UNION query

[Tom Lane]

Richard Huxton  writes:
> Antony Paul wrote:
>> I need to use ORDER BY clause in a UNION query and the Order BY
>> columns are not included in the SELECT statement. I tried like this
>> 
>> (select  from a) UNION (select . from b) order by a.ename;
>> 
>> It says that 
>> ERROR:  Attribute "ename" not found

> The "order by" is applying to the results of the union, not one of the 
> sub-selects. If you want to sort by a value, you'll need to include it 
> in the results list.

You could suppress the order-by fields after the fact:

SELECT x,y,z FROM
  ( (SELECT x,y,z,q FROM a)
UNION
(SELECT x,y,z,q FROM b)
ORDER BY q
  ) ss;

Also, always ask yourself if you really need UNION or if UNION ALL
is sufficient.  Removing duplicates from a large UNION is *expensive*,
and all too often a waste of time.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] Problem creating trigger-function with arguments (8.0rc4)

[Florian G. Pflug]

Michael Fuhr wrote:
On Fri, Jan 07, 2005 at 09:00:12PM +0100, Florian G. Pflug wrote:
CREATE OR REPLACE FUNCTION functions.t_insert_deny(v_message text)
RETURNS "trigger" AS $$
See the "Trigger Procedures" section of the PL/pgSQL documentation.
The first paragraph contains this:
Note that the function must be declared with no arguments even if
it expects to receive arguments specified in CREATE TRIGGER ---
trigger arguments are passed via TG_ARGV, as described below.
Seems I should have RTFMed more ;-). I believe I even read this 
paragraph, but thought this refers to C-Functions, not plpgsql ones.
Thanks for pointing this out.

If I replace RETURNS "trigger" with e.g. RETURNS "int8" it works...
Is this is a bug, or has something regarding triggerfunctions and
parameters changed in 8.0
Changed since when?  Are you saying this worked in an older version
of PostgreSQL?  If so, what version?  The paragraph I quoted above
goes back to at least 7.2.
I didn't test on anything other than 8.0 - but I used the 7.4 docu, not 
the 8.0 one, and since the docu says that trigger functions _can_ take 
parameters, I somehow believed that it has to be possible to _declare_
those arguments - Well, guess I should read more carefully ;-)

greetings, Florian Pflug


smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] could not open relation No such file or directory after alter table

[Tom Lane]

Chris <[EMAIL PROTECTED]> writes:
> This is Postgresql 8 RC 1 on freebsd 5.3. 
> I changed the type of a column from varchar(128) to varchar(256), and
> right after that I got the following messages when doing inserts on
> the table in question.

> <41e0e2c3.fd54>ERROR:  SMgrRelation hashtable corrupted
> <41e0e2c3.fd54>STATEMENT:  commit

Hmm.  We got another report of that back in September, but AFAICS from
the archives it was never resolved because we couldn't reproduce it.
Can you come up with a reproducible test case?

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Transaction size

[Alban Hertroys]

Tom Lane wrote:
Alban Hertroys <[EMAIL PROTECTED]> writes:
It does have a reference to a table with statusses, but those are rather 
static. I suppose an integrity check is comparable to doing a select 
with respect to locking strategies? (Meaning that it wouldn't be the 
cause of my problem).
No, unfortunately it's more like a SELECT FOR UPDATE and it does take a
lock on the referenced row (with an eye to ensuring that the referenced
row can't go away before the new referencing row is committed).
I suspect this is indeed the cause of your problem.
I read this after solving the problem, but this was indeed happening 
(though I didn't know that while solving it). We had cleaned up some 
superfluous commits, but were a bit too enthousiastic about it and 
removed the commits at the ends of the threads involved.

This resulted in a DB operation that never ended (until the thread would 
have ended, which never happened because of this), because the commit 
didn't take place, resulting on other threads waiting for this lock to 
be released.

I now don't just understand why the threads never ended, I also 
understand why it locked up at that particular point.

Thank you very much for your wisdom.
Regards,
Alban Hertroys.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] handing created and updated fields

[Daniel Martini]

Hi,

Citing "Jim C. Nasby" <[EMAIL PROTECTED]>:
> ON INSERT: force created and updated to be current_timestamp
> ON UPDATE: deny updated created. force updated to be set to
> current_timestamp
[snip]
> Does anyone have an example of the best way to handle this scenario?

Something along the lines of the following should work (but test first
anyways, though I have copied smaller parts of this from the definitions
in one of my databases here, I have made modifications to fit your 
specific task, so typos/errors might have sneaked in):

create function update_trigger() returns trigger as
'begin
new.created := old.created;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';

create trigger update_trigger BEFORE UPDATE ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE update_trigger();

create function insert_trigger() returns trigger as
'begin
new.created := CURRENT_TIMESTAMP;
new.updated := CURRENT_TIMESTAMP;
return new;
end;'
language 'plpgsql';

create trigger insert_trigger BEFORE INSERT ON your_table_name
FOR EACH ROW EXECUTE PROCEDURE insert_trigger();

HTH,
Regards,
Daniel

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Alex Turner]

Forgive my ignorance, but I'm still learning about much of this stuff.
 If you perform:

select an_id, int_value from my_table where int_value>400;

The table has an index on int_value and there are enough rows to
warrant using it.  Doesn't the database perform in index scan on
int_value followed by a retrieve for the datablocks with relavent oids
to get the an_id field?

If another transaction has inserted rows into this table, won't the
index have been updated, and contain new row references?  Does this
imply that the database must retrieve the row information to determine
if the row is a row from a different transaction?

thanks,

Alex Turner


On Sat, 08 Jan 2005 12:39:41 -0700, Scott Ribe
<[EMAIL PROTECTED]> wrote:
> > No offense or anything, but that doesn't make any sense.  If you are
> > running count(*) against a table, it still has to worry about MVCC,
> > and which rows are visible to your transaction.  What difference does
> > it make, table or index, the system still has to figure out which rows
> > are visible in the current transaction, so why not use the index?
> 
> Your mistake seems to be assuming that row visibility is tracked in the
> index. As was stated earlier in the thread, row visibility information is
> not available in the index, therefore rows have to be looked at to determine
> whether they're visible. What this means is that using the index would only
> add an additional unnecessary step.
> 
> > (The example is really count(pkey) because count(*) is always going to
> > do a seq scan I reckon - and could probably never use an index).
> 
> No, if there is an index on a column that is required, such as a primary
> key, then count(pkey) is equal to count(*). Many databases make use of this
> fact to optimize performance of count(*) by using an index scan.
> 
> --
> Scott Ribe
> [EMAIL PROTECTED]
> http://www.killerbytes.com/
> (303) 665-7007 voice
> 
>

---(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: [GENERAL] handing created and updated fields

[Sven Willenberger]

On Mon, 2005-01-10 at 15:45 +0100, Daniel Martini wrote:
> Hi,
> 
> Citing "Jim C. Nasby" <[EMAIL PROTECTED]>:
> > ON INSERT: force created and updated to be current_timestamp
> > ON UPDATE: deny updated created. force updated to be set to
> > current_timestamp
> [snip]
> > Does anyone have an example of the best way to handle this scenario?
> 
> Something along the lines of the following should work (but test first
> anyways, though I have copied smaller parts of this from the definitions
> in one of my databases here, I have made modifications to fit your 
> specific task, so typos/errors might have sneaked in):
> 
> create function update_trigger() returns trigger as
> 'begin
> new.created := old.created;
> new.updated := CURRENT_TIMESTAMP;
> return new;
> end;'
> language 'plpgsql';
> 
> create trigger update_trigger BEFORE UPDATE ON your_table_name
> FOR EACH ROW EXECUTE PROCEDURE update_trigger();
> 
> create function insert_trigger() returns trigger as
> 'begin
> new.created := CURRENT_TIMESTAMP;
> new.updated := CURRENT_TIMESTAMP;
> return new;
> end;'
> language 'plpgsql';
> 
> create trigger insert_trigger BEFORE INSERT ON your_table_name
> FOR EACH ROW EXECUTE PROCEDURE insert_trigger();
> 
> HTH,
> Regards,
> Daniel

These could also be combined into one trigger since they are nearly
identical anyway:

CREATE FUNCTION combined_trigger() RETURNS TRIGGER AS '
BEGIN
   NEW.update := CURRENT_TIMESTAMP;
   IF TG_OP = ''INSERT'' THEN
  NEW.created := CURRENT_TIMESTAMP;
   ELSE
  NEW.created := OLD.created;
   END IF;
   RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER combined_trigger BEFORE INSERT OR UPDATE on
your_table_name FOR EACH ROW EXECUTE PROCEDURE combined_trigger();

Sven


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Bruno Wolff III]

On Mon, Jan 10, 2005 at 10:26:46 -0500,
  Alex Turner <[EMAIL PROTECTED]> wrote:
> Forgive my ignorance, but I'm still learning about much of this stuff.
>  If you perform:
> 
> select an_id, int_value from my_table where int_value>400;
> 
> The table has an index on int_value and there are enough rows to
> warrant using it.  Doesn't the database perform in index scan on
> int_value followed by a retrieve for the datablocks with relavent oids
> to get the an_id field?

I don't think that oids are used in the process, but if the planner thinks
an index scan would be better it will use one.

> If another transaction has inserted rows into this table, won't the
> index have been updated, and contain new row references?  Does this
> imply that the database must retrieve the row information to determine
> if the row is a row from a different transaction?

When doing an index scan, the heap tuples still need to be checked for
visibility to the current transaction.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Function for retreiving datatype

[Brendan Jurd]

Does postgres have a function to determine the data type of an 
argument?  I'm looking for something analogous to PHP's gettype 
function.  I had a look through the documentation and did a few likely 
pattern searches with \df.  Nothing came up.

Apologies in advance if the answer is obvious.
Cheers
BJ
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Alex Turner]

I'm no database writing guru, but wouldn't it just be a matter of
adding a transaction number to an index entry so as to determine it's
newness and only retrieve entries with an older transaction number?

I'm guessing that the theory is that most insert transactions will be
committed, or only contain a small number of rows relative to the
overall size of the table, and therefore the extra overhead of
checking newer tuples won't impact the overall performance that much?

I know I'm asking kind of deep questions that really don't affect much
of anything, but I'm a devilishly curious individual, and I like
understanding things that I use well.  Feel free to tell me that it's
irrelavant, or that I'm full of hot air and I don't have a good
question ;)

Alex Turner
NetEconomist


On Mon, 10 Jan 2005 10:34:46 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Mon, Jan 10, 2005 at 10:26:46 -0500,
>   Alex Turner <[EMAIL PROTECTED]> wrote:
> > Forgive my ignorance, but I'm still learning about much of this stuff.
> >  If you perform:
> >
> > select an_id, int_value from my_table where int_value>400;
> >
> > The table has an index on int_value and there are enough rows to
> > warrant using it.  Doesn't the database perform in index scan on
> > int_value followed by a retrieve for the datablocks with relavent oids
> > to get the an_id field?
> 
> I don't think that oids are used in the process, but if the planner thinks
> an index scan would be better it will use one.
> 
> > If another transaction has inserted rows into this table, won't the
> > index have been updated, and contain new row references?  Does this
> > imply that the database must retrieve the row information to determine
> > if the row is a row from a different transaction?
> 
> When doing an index scan, the heap tuples still need to be checked for
> visibility to the current transaction.
> 
>

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Alvaro Herrera]

On Mon, Jan 10, 2005 at 11:51:51AM -0500, Alex Turner wrote:
> I'm no database writing guru, but wouldn't it just be a matter of
> adding a transaction number to an index entry so as to determine it's
> newness and only retrieve entries with an older transaction number?

No, it's more complex than that.  Index entries would have to be labeled
with both a creation transaction Id and a destruction transaction Id
(xmin and xmax.  Probably it'd also need Cmin and Cmax to be completely
consistent.)  Keeping them in sync would be prone to deadlock because
it'd have to simultaneously update the table proper and the possibly
multiple indexes.  Plus, having all those identifiers in the index file
would imply more I/O costs.

> I'm guessing that the theory is that most insert transactions will be
> committed, or only contain a small number of rows relative to the
> overall size of the table, and therefore the extra overhead of
> checking newer tuples won't impact the overall performance that much?

The choice is yours (or whoever's): if you absolutely need exact
numbers, you pay the cost of having a trigger.  OTOH if you can do with
estimates, you can use the reltuples column from pg_class.  

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
"There is evil in the world. There are dark, awful things. Occasionally, we get
a glimpse of them. But there are dark corners; horrors almost impossible to
imagine... even in our worst nightmares." (Van Helsing, Dracula A.D. 1972)

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Bruno Wolff III]

On Mon, Jan 10, 2005 at 11:51:51 -0500,
  Alex Turner <[EMAIL PROTECTED]> wrote:
> I'm no database writing guru, but wouldn't it just be a matter of
> adding a transaction number to an index entry so as to determine it's
> newness and only retrieve entries with an older transaction number?

No, because transactions don't complete in the order they are started.
So you will need to be able to maintain some kind of list to cover
exceptions.

> I'm guessing that the theory is that most insert transactions will be
> committed, or only contain a small number of rows relative to the
> overall size of the table, and therefore the extra overhead of
> checking newer tuples won't impact the overall performance that much?
> 
> I know I'm asking kind of deep questions that really don't affect much
> of anything, but I'm a devilishly curious individual, and I like
> understanding things that I use well.  Feel free to tell me that it's
> irrelavant, or that I'm full of hot air and I don't have a good
> question ;)

There have been discussions in the past about why the core developers
feel that moving visibility status into indexes would be a net loss
on average. I don't think there has been one for a while, but you can
try searching the hackers archive.

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

[GENERAL] SELECT from multiple tables (not join though)

[Madison Kelly]

Hi all,
  I have another question, I hope it isn't too basic. ^.^
  I want to do a select from multiple tables but not join them. What I 
am trying to do is something like this (though this doesn't work as I need):

SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a, 
file_info_2 b, file_info_3 c WHERE a.file_name='/' AND 
b.file_parent_dir='/' AND c.file_parent_dir='/';

  That returns every combination of the results from the three tables 
which is a huge number. What I need is to return all of the matches in 
all of the tables in a single column. Once I have all the matches in one 
column the next trick is to sort the combined results (any tips there?).

  I hope the question was clear. Please let me know if it wasn't. Thanks!
Madison
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Function for retreiving datatype

[Michael Fuhr]

On Tue, Jan 11, 2005 at 03:28:08AM +1100, Brendan Jurd wrote:

> Does postgres have a function to determine the data type of an 
> argument?

In what context?  What problem are you trying to solve?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] SELECT from multiple tables (not join though)

[John Sidney-Woollett]

I hope I've read your question properly - I seem to be giving answers to 
un-asked questions lately! ;)

How about...
SELECT file_name from file_info_1 WHERE file_name='/'
union
SELECT file_name from file_info_2 WHERE file_parent_name='/'
union
SELECT file_name from file_info_3 WHERE file_parent_name='/'
order by file_name;
Does that do what you want?
John Sidney-Woollett
Madison Kelly wrote:
Hi all,
  I have another question, I hope it isn't too basic. ^.^
  I want to do a select from multiple tables but not join them. What I 
am trying to do is something like this (though this doesn't work as I 
need):

SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a, 
file_info_2 b, file_info_3 c WHERE a.file_name='/' AND 
b.file_parent_dir='/' AND c.file_parent_dir='/';

  That returns every combination of the results from the three tables 
which is a huge number. What I need is to return all of the matches in 
all of the tables in a single column. Once I have all the matches in one 
column the next trick is to sort the combined results (any tips there?).

  I hope the question was clear. Please let me know if it wasn't. Thanks!
Madison
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] SELECT from multiple tables (not join though)

[Madison Kelly]

Typo, that should have been:
SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a, 
file_info_2 b, file_info_3 c WHERE a.file_parent_dir='/' AND 
b.file_parent_dir='/' AND c.file_parent_dir='/';

(All the WHERE... are the same)
Madison
Madison Kelly wrote:
Hi all,
  I have another question, I hope it isn't too basic. ^.^
  I want to do a select from multiple tables but not join them. What I 
am trying to do is something like this (though this doesn't work as I 
need):

SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a, 
file_info_2 b, file_info_3 c WHERE a.file_name='/' AND 
b.file_parent_dir='/' AND c.file_parent_dir='/';

  That returns every combination of the results from the three tables 
which is a huge number. What I need is to return all of the matches in 
all of the tables in a single column. Once I have all the matches in one 
column the next trick is to sort the combined results (any tips there?).

  I hope the question was clear. Please let me know if it wasn't. Thanks!
Madison
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

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

Re: [GENERAL] SELECT from multiple tables (not join though)

[Alex Turner]

To be honest, it looks like you might need a schema adjustment. 
Normalization means keeping one kind of thing in one place, avoiding
ugly queries.  Inheritance can also help with this too.

Alex Turner
NetEconomist


On Mon, 10 Jan 2005 12:22:41 -0500, Madison Kelly <[EMAIL PROTECTED]> wrote:
> Hi all,
> 
>I have another question, I hope it isn't too basic. ^.^
> 
>I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I need):
> 
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
> 
>That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in one
> column the next trick is to sort the combined results (any tips there?).
> 
>I hope the question was clear. Please let me know if it wasn't. Thanks!
> 
> Madison
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings
>

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] SELECT from multiple tables (not join though)

[Michael Fuhr]

On Mon, Jan 10, 2005 at 12:22:41PM -0500, Madison Kelly wrote:

> What I need is to return all of the matches in all of the tables
> in a single column.

Maybe you're looking for UNION -- see the "Combining Queries" section
in the "Queries" chapter of the documentation.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] SELECT from multiple tables (not join though)

[Madison Kelly]

John Sidney-Woollett wrote:
I hope I've read your question properly - I seem to be giving answers to 
un-asked questions lately! ;)

How about...
SELECT file_name from file_info_1 WHERE file_name='/'
union
SELECT file_name from file_info_2 WHERE file_parent_name='/'
union
SELECT file_name from file_info_3 WHERE file_parent_name='/'
order by file_name;
Does that do what you want?
John Sidney-Woollett
That worked perfectly, though now I realize I have another problem that 
is hopefully easy to get around. Here is my query (with the type fixed):

SELECT file_name FROM file_info_1 WHERE file_parent_dir='/' UNION SELECT 
file_name FROM file_info_2 WHERE file_parent_dir='/' UNION SELECT 
file_name FROM file_info_3 WHERE file_parent_dir='/' ORDER BY file_name;

The trick now is I need to know which table each result came from. I can 
add another column and record the table number and SELECT that at the 
same time but before I do I was wondering if I can do this more 
efficiently or elegantly.

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

Re: [GENERAL] SELECT from multiple tables (not join though)

[Janning Vygen]

Am Montag, 10. Januar 2005 18:22 schrieb Madison Kelly:
> Hi all,
>
>I have another question, I hope it isn't too basic. ^.^
>
>I want to do a select from multiple tables but not join them. What I
> am trying to do is something like this (though this doesn't work as I
> need):
>
> SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a,
> file_info_2 b, file_info_3 c WHERE a.file_name='/' AND
> b.file_parent_dir='/' AND c.file_parent_dir='/';
>
>That returns every combination of the results from the three tables
> which is a huge number. What I need is to return all of the matches in
> all of the tables in a single column. Once I have all the matches in one
> column the next trick is to sort the combined results (any tips there?).

you want something like this

SELECT a.file_name
FROM file_info_1 a
WHERE a.file_name='/' 

UNION

SELECT b.file_name
FROM file_info_2 b
WHERE b.file_name='/' 

UNION

SELECT c.file_name
FROM file_info_3 c
WHERE c.file_name='/' 

ORDER BY 1;

for further documentation visit 

 http://www.postgresql.org/docs/7.4/interactive/sql-select.html

or your local postgresql documentation.

kind regards,
janning


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] SELECT from multiple tables (not join though)

[Frank D. Engel, Jr.]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
As for the first part, try this:
(SELECT file_name FROM file_info_1 WHERE file_name = '/') UNION
(SELECT file_name FROM file_info_2 WHERE file_parent_dir = '/') UNION
(SELECT file_name FROM file_info_3 WHERE file_parent_dir = '/')
As for sorting the combined results, it would be along the lines of the 
following, although I suspect I am missing something here (untested, if 
it doesn't work, someone else might know how to fix it for you):

SELECT file_name
FROM ((SELECT file_name FROM file_info_1 WHERE file_name = '/') UNION
  (SELECT file_name FROM file_info_2 WHERE file_parent_dir 
= '/') UNION
  (SELECT file_name FROM file_info_3 WHERE file_parent_dir 
= '/')) AS a
ORDER BY file_name

On Jan 10, 2005, at 12:22 PM, Madison Kelly wrote:
Hi all,
  I have another question, I hope it isn't too basic. ^.^
  I want to do a select from multiple tables but not join them. What I 
am trying to do is something like this (though this doesn't work as I 
need):

SELECT a.file_name, b.file_name, c.file_name FROM file_info_1 a, 
file_info_2 b, file_info_3 c WHERE a.file_name='/' AND 
b.file_parent_dir='/' AND c.file_parent_dir='/';

  That returns every combination of the results from the three tables 
which is a huge number. What I need is to return all of the matches in 
all of the tables in a single column. Once I have all the matches in 
one column the next trick is to sort the combined results (any tips 
there?).

  I hope the question was clear. Please let me know if it wasn't. 
Thanks!

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


- ---
Frank D. Engel, Jr.  <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB4r6/7aqtWrR9cZoRAgxhAJ9p1tJBs+xmlZ/TlgKVOaAC+FtCEACfa+1g
Uf8dStwt9O2hwlP56chWabk=
=a+F/
-END PGP SIGNATURE-

___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] SELECT from multiple tables (not join though)

[Alvaro Herrera]

On Mon, Jan 10, 2005 at 12:47:53PM -0500, Madison Kelly wrote:

Madison,

> The trick now is I need to know which table each result came from. I can 
> add another column and record the table number and SELECT that at the 
> same time but before I do I was wondering if I can do this more 
> efficiently or elegantly.

You can get the Oid of the table very easily with the "tableoid" column.
If you want the name you can use tableoid::regclass, though I'm not sure
if that works as a normal text column.

Be sure to grok the difference between UNION and UNION ALL (which is the
fact that UNION sorts and "uniqs" its input).

-- 
Alvaro Herrera (<[EMAIL PROTECTED]>)
Oh, oh, las chicas galacianas, lo harán por las perlas,
¡Y las de Arrakis por el agua! Pero si buscas damas
Que se consuman como llamas, ¡Prueba una hija de Caladan! (Gurney Halleck)

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

   http://archives.postgresql.org

Re: [GENERAL] SELECT from multiple tables (not join though)

[Madison Kelly]

Alex Turner wrote:
To be honest, it looks like you might need a schema adjustment. 
Normalization means keeping one kind of thing in one place, avoiding
ugly queries.  Inheritance can also help with this too.

Alex Turner
NetEconomist
Hi,
  The schema started off with all the data in one place as you 
described but in this case I kind of had to split out the data into 
different tables in order to win pretty major performance gains 
elsewhere. By Inheritance you mean using pkeys and such, right? I 
haven't looked into using pkeys and such yet... I haven't seen a 
compelling reason to in my app yet, this may be it?

Thanks for your reply!
Madison
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] SELECT from multiple tables (not join though)

[Bruno Wolff III]

On Mon, Jan 10, 2005 at 12:47:53 -0500,
  Madison Kelly <[EMAIL PROTECTED]> wrote:
> 
> The trick now is I need to know which table each result came from. I can 
> add another column and record the table number and SELECT that at the 
> same time but before I do I was wondering if I can do this more 
> efficiently or elegantly.

You can add a constant to the select list in each of the subselects
that indicates which table is being used.

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

Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 03:28:08AM +1100, Brendan Jurd wrote:
 

Does postgres have a function to determine the data type of an 
argument?
   

In what context?  What problem are you trying to solve?
 

Well, I solved the original problem in a different way, but I'd still
like to know whether such a function exists.
The original problem had to do with querying a row-returning function.
I had an SQL function that returned "SETOF record", and I was trying to
use it in the FROM clause of a query.  To do so, you need to provide a
list of column definitions.  I was getting the error about the returned
row types not matching my column defs.  In the end it was a simple
mistake -- I had specified 'text' where I should have specified
'varchar'.  I had thought to use some kind of "gettype" function to find
out exactly what data types my query was returning.
On that note, it might be helpful to increase the verbosity of the
"returned row types" error message, so that it actually explains the
mismatch it encountered.  Something like "Returned column 3 is
varchar(15) but column definition is text" would have made debugging a
whole lot easier.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] SELECT from multiple tables (not join though)

[Madison Kelly]

Bruno Wolff III wrote:
On Mon, Jan 10, 2005 at 12:47:53 -0500,
  Madison Kelly <[EMAIL PROTECTED]> wrote:
The trick now is I need to know which table each result came from. I can 
add another column and record the table number and SELECT that at the 
same time but before I do I was wondering if I can do this more 
efficiently or elegantly.

You can add a constant to the select list in each of the subselects
that indicates which table is being used.
^.^; Can you point me to docs that will help me learn how to do that? 
Thanks! Or rather, do you mean add a column to the table with an ID for 
the table that I select beside the file_name? If so, that is what I am 
planning to do if I can't find a smoother way to do it.

Thanks!!
Madison
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [GENERAL] SELECT from multiple tables (not join though)

[Derik Barclay]

SELECT 'table1' AS source, file_name FROM file_info_1 WHERE 
file_parent_dir='/' 
UNION 
SELECT 'table2' AS source, file_name FROM file_info_2 WHERE 
file_parent_dir='/' 
UNION 
SELECT 'table3' AS source, file_name FROM file_info_3 WHERE 
file_parent_dir='/' ORDER BY file_name;

On January 10, 2005 01:24 pm, Madison Kelly wrote:
> Bruno Wolff III wrote:
> > On Mon, Jan 10, 2005 at 12:47:53 -0500,
> >
> >   Madison Kelly <[EMAIL PROTECTED]> wrote:
> >>The trick now is I need to know which table each result came from. I can
> >>add another column and record the table number and SELECT that at the
> >>same time but before I do I was wondering if I can do this more
> >>efficiently or elegantly.
> >
> > You can add a constant to the select list in each of the subselects
> > that indicates which table is being used.
>
> ^.^; Can you point me to docs that will help me learn how to do that?
> Thanks! Or rather, do you mean add a column to the table with an ID for
> the table that I select beside the file_name? If so, that is what I am
> planning to do if I can't find a smoother way to do it.
>
> Thanks!!
>
> Madison
>
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org

-- 
Givex - http://www.givex.com/
Derik Barclay <[EMAIL PROTECTED]>, Systems Software Engineer
+1 416 350 9660
+1 416 250 9661 (fax)

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

Re: [GENERAL] SELECT from multiple tables (not join though)

[Bruno Wolff III]

On Mon, Jan 10, 2005 at 13:24:50 -0500,
  Madison Kelly <[EMAIL PROTECTED]> wrote:
> 
> ^.^; Can you point me to docs that will help me learn how to do that? 
> Thanks! Or rather, do you mean add a column to the table with an ID for 
> the table that I select beside the file_name? If so, that is what I am 
> planning to do if I can't find a smoother way to do it.

You can just add a list item. Either a number or a quoted string.
Something like:
SELECT 'Table A', col1, col2, col3 FROM tablea;

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

Re: [GENERAL] how to optimize my c-extension functions

[TJ O'Donnell]

I was not hoping that indexing, per se, would help me.
In fact, indexing smiles would be of virtually no use
to me, except for exact matches, e.g. where smiles = 'CCCOC';
I was only trying to subvert the use of indexing for
my own purposes, to store the parsed smiles somewhere
automatic for the sql user, yet transparently available to my
functions for quick searches.
I think I've thought about this enough and gotten enough advice
to realize I should do this the straightforward way.
I should store the parsed smiles in a separate column,
have a trigger to keep it up to date, and require the
user to pass me the parsed_smiles column for quick searches.
And the user could maintain the parsed_smiles in a separate
table, if he so desired, with foreign key relations.
Thanks to everyone for all your advice.  This is my first
postgresql project and I'm liking what I've seen so far.
TJ
Tom Lane wrote:
"TJ O'Donnell" <[EMAIL PROTECTED]> writes:
The only type of search will be of the type:

Select smiles,id from structure where  oe_matches(smiles,'c1c1C(=O)N');

You haven't really said much about how you expect an index to be able to
help you with this, but I think if any index type can help you it will
be GiST.  What you would do is define an operator on top of the
oe_matches function, so that the above query is written say
Select smiles,id from structure where smiles ~~ 'c1c1C(=O)N';
and then construct a GiST operator class that accepts ~~ as an
indexable operator.  There's not a huge amount of
plain-old-documentation about GiST but there are quite a few examples
available in the contrib/ tree.
I don't think you can completely hide the existence of the parsed
version of the smiles data.  The easiest way to go at it would be to
write the queries like
Select smiles,id from structure where smiles_parsed ~~ 'c1c1C(=O)N';
where smiles_parsed is the extra column holding the parsed data, and
the ~~ operator is grabbed by a GiST index over that column.
Plan B would be to construct the index as a functional index and write
Select smiles,id from structure where parsed(smiles) ~~ 'c1c1C(=O)N';
However plan B doesn't readily support applying any other operations to
the parsed data, since it doesn't exist anywhere except inside the
index.  Since you mentioned having other things you wanted to do with it,
I think you'll end up wanting the separate column.
			regards, tom lane
---(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: [GENERAL] SELECT from multiple tables (not join though)

[Frank D. Engel, Jr.]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
SELECT file_name, 1 FROM file_info_1 WHERE file_parent_dir='/' UNION
SELECT file_name, 2 FROM file_info_2 WHERE file_parent_dir='/' UNION
SELECT file_name, 3 FROM file_info_3 WHERE file_parent_dir='/'
ORDER BY file_name;
The second column now shows which table it came from.  No need to mess 
with adding fields, OIDs, etc...

On Jan 10, 2005, at 1:24 PM, Madison Kelly wrote:
Bruno Wolff III wrote:
On Mon, Jan 10, 2005 at 12:47:53 -0500,
  Madison Kelly <[EMAIL PROTECTED]> wrote:
The trick now is I need to know which table each result came from. I 
can add another column and record the table number and SELECT that 
at the same time but before I do I was wondering if I can do this 
more efficiently or elegantly.
You can add a constant to the select list in each of the subselects
that indicates which table is being used.
^.^; Can you point me to docs that will help me learn how to do that? 
Thanks! Or rather, do you mean add a column to the table with an ID 
for the table that I select beside the file_name? If so, that is what 
I am planning to do if I can't find a smoother way to do it.

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

  http://archives.postgresql.org

- ---
Frank D. Engel, Jr.  <[EMAIL PROTECTED]>
$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten 
Son, that whosoever believeth in him should not perish, but have 
everlasting life.
$
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB4s/o7aqtWrR9cZoRAuYtAJ95XxbFAcfK5MBDU+sC4ktulxqfwwCfbIfM
mjQKofx230j5myapOSbGCAc=
=WEWW
-END PGP SIGNATURE-

___
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com
---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [GENERAL] Function for retreiving datatype

[Michael Fuhr]

On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:
> 
> The original problem had to do with querying a row-returning function.
> I had an SQL function that returned "SETOF record", and I was trying to
> use it in the FROM clause of a query.  To do so, you need to provide a
> list of column definitions.  I was getting the error about the returned
> row types not matching my column defs.  In the end it was a simple
> mistake -- I had specified 'text' where I should have specified
> 'varchar'.  I had thought to use some kind of "gettype" function to find
> out exactly what data types my query was returning.

Where would you call this gettype() function from?  It seems like
you have a chicken-and-egg situation: you need to provide a column
definition list when you issue the query, but you don't know what
the return row will look like until the query executes the function.
In the current implementation, if a function returns SETOF RECORD
then you need to know in advance what columns a particular invocation
of that function will return.

> On that note, it might be helpful to increase the verbosity of the
> "returned row types" error message, so that it actually explains the
> mismatch it encountered.  Something like "Returned column 3 is
> varchar(15) but column definition is text" would have made debugging a
> whole lot easier.

Consider suggesting that to the developers.  I'm not sure what the
best list would be -- maybe pgsql-bugs if you consider the terse
message to be a bug, or maybe pgsql-hackers since it's a proposed
enhancement.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

   http://archives.postgresql.org

Re: [GENERAL] SELECT from multiple tables (not join though)

[Madison Kelly]

Frank D. Engel, Jr. wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
SELECT file_name, 1 FROM file_info_1 WHERE file_parent_dir='/' UNION
SELECT file_name, 2 FROM file_info_2 WHERE file_parent_dir='/' UNION
SELECT file_name, 3 FROM file_info_3 WHERE file_parent_dir='/'
ORDER BY file_name;
The second column now shows which table it came from.  No need to mess 
with adding fields, OIDs, etc...
Thank you Frank, Bruno and Derik,
  That worked wonderfully! Derik's suggestion also worked perfectly, 
too. I would imagine this method is slightly faster that his because I 
am not really assigning anything? I guess I will need to run some 
benchmarks. It is really nice to have options though.

  Thank you all again! The amount of time you guys have saved me since 
I joined is just phenominal!

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

Re: [GENERAL] Function for retreiving datatype

[Pierre-Frédéric Caillaud]

Example :
psql
create table test (id serial primary key, data10 varchar(10), data20  
varchar(20), data text );
insert into test (data10, data20, data) values ('ten','twenty','all i  
want');

python
import psycopg
db = psycopg.connect("host=localhost dbname=.")
c = db.cursor()
c.execute( "SELECT * FROM test LIMIT 1;" )
print c.description
(('id', 23, None, 4, None, None, None), ('data10', 1043, None, 10, None,  
None, None), ('data20', 1043, None, 20, None, None, None), ('data', 25,  
None, -1, None, None, None))

Here the integer behind the name is the type-id, the next one which is not  
None is the length.
Lets paste the typids in postgres :

=> select typname,typelem from pg_type where typelem in (23,25,1043);
 typname  | typelem
--+-
 _int4|  23
 _text|  25
 _varchar |1043
Using this you can easily print the types returned by whatever :
python
c.execute('rollback')
c.execute( "SELECT typelem,typname FROM pg_type WHERE typelem != 0" )
typmap = dict(c.fetchall())
c.execute( "SELECT * FROM test LIMIT 1;" )
print "\n".join(["%s\t: %s\t%d" % (field_name, typmap[typid], typlen) for  
field_name,typid,_,typlen,_,_,_ in c.description])
id  : _int4 4
data10  : _varchar  10
data20  : _varchar  20
data: _text -1

c.dictfetchall()
[{'data20': 'twenty', 'data': 'all i want', 'id': 1, 'data10': 'ten'}]
Don't ask me what the remaining things returned in c.description are, I  
don't know. Read the docs.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] does "select count(*) from mytable" always do a seq

[Michael Fuhr]

On Mon, Jan 10, 2005 at 11:23:42AM -0600, Bruno Wolff III wrote:

> There have been discussions in the past about why the core developers
> feel that moving visibility status into indexes would be a net loss
> on average. I don't think there has been one for a while, but you can
> try searching the hackers archive.

I wonder if a technical discussion or a link to one could find its
way into the FAQ.  Has anybody ever put together a single document
that describes in detail the issue, various proposed solutions and
their tradeoffs, and why the developers think the chosen implementation
is best?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] how to optimize my c-extension functions

[Pierre-Frédéric Caillaud]

That's not what I meant...
	I meant, what does 'c1c1C(=O)N' means ?
	If the search operation is too slow, you can narrow it using standard  
postgres tools and then hand it down to your C functions. Let me explain,  
I have no clue about this 'c1c1C(=O)N' syntax, but I'll suppose you  
will be searching for things like :

1- molecule has N atoms of (whatever) element
2- molecule has N single or double or triple covalent bonds
3- molecule has such and such property
	Then, if you can understand the 'c1c1C(=O)N' string and say that all  
molecules that satisfy it will satisfy, for instance condition 2 above,  
then you can have some fast searchable attributes in your database that  
will mark all molecules satisfying condition 2, and you'll only need to  
run the C search function on these to get the real matches.
	The idea is basically to narrow down the search to avoid calling the  
expensive operator on all rows.

	If A and B and strings like your 'c1c1C(=O)N', then if all molecules  
satsfying B also satisfy A (thus B=>A or "B c A", B is contained in A in  
set notation), if you can very quickly (with an index) grab the molecules  
that satisfy A, and these are a significantly smaller number than the  
whole set, then you'll speed your search a lot.
	If you can find some more A's, so that B c A1, B c A2, B c A3, then B c  
(intersection of A1, A2, A3) which maps neatly to the gist index on an  
integer array.
	So you could have a set of basic conditions, maybe a hundred or so, which  
would be all tested on the search string to see which will apply to the  
molecules this search string would find, then you translate this into a  
GiST query.

Are my explications making it clearer or just more obfuscated ?

The only type of search will be of the type:
Select smiles,id from structure where   
oe_matches(smiles,'c1c1C(=O)N');

or joins with other tables e.g.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] handing created and updated fields

[Jim C. Nasby]

On Mon, Jan 10, 2005 at 11:16:03AM -0500, Sven Willenberger wrote:
> These could also be combined into one trigger since they are nearly
> identical anyway:
> 
> CREATE FUNCTION combined_trigger() RETURNS TRIGGER AS '
> BEGIN
>NEW.update := CURRENT_TIMESTAMP;
>IF TG_OP = ''INSERT'' THEN
>   NEW.created := CURRENT_TIMESTAMP;
>ELSE
>   NEW.created := OLD.created;
>END IF;
>RETURN NEW;
> END;
> ' LANGUAGE plpgsql;
 
Excellent; any idea which would perform better (combined v. separate
trigger function)?
-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

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

[GENERAL] Link to development version of docs on website?

[Chris]

I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?

Chris

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

[GENERAL] large dbII to postgresql migration

[marcelo Cortez]

hello to all 

Soon time I am going to face a migration of a DBII
database our dear postgresql .
I will thank for any advice that can give me, in
addition this migration
will have 20 million records.
20 million record is to large instalation, exist some
tips for this type of facilities?
Tia .
Best regards

  Klingon








___ 
250MB gratis, Antivirus y Antispam 
Correo Yahoo!, el mejor correo web del mundo 
http://correo.yahoo.com.ar

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

   http://archives.postgresql.org

Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

Michael Fuhr wrote:

  On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:
  
  
The original problem had to do with querying a row-returning function.
I had an SQL function that returned "SETOF record", and I was trying to
use it in the FROM clause of a query.  To do so, you need to provide a
list of column definitions.  I was getting the error about the returned
row types not matching my column defs.  In the end it was a simple
mistake -- I had specified 'text' where I should have specified
'varchar'.  I had thought to use some kind of "gettype" function to find
out exactly what data types my query was returning.

  
  
Where would you call this gettype() function from?  It seems like
you have a chicken-and-egg situation: you need to provide a column
definition list when you issue the query, but you don't know what
the return row will look like until the query executes the function.
In the current implementation, if a function returns SETOF RECORD
then you need to know in advance what columns a particular invocation
of that function will return.

  

Not really an issue.  I could have yanked the source query out of the
row-returning function, planted it into a regular console, and wrapped
the hypothetical gettype() function around the individual columns to
test the type of their output.

But that's getting away from the point.  It doesn't really matter
whether I could have used gettype() to solve that particular problem. 
Which is why I didn't bring it up in my original post.  My post was all
about finding out whether postgres has this functionality.  If it does,
and I just wasn't looking hard enough, it's all good.  If it doesn't,
I'd like to explore the possibility of getting it added in.


  
  
On that note, it might be helpful to increase the verbosity of the
"returned row types" error message, so that it actually explains the
mismatch it encountered.  Something like "Returned column 3 is
varchar(15) but column definition is text" would have made debugging a
whole lot easier.

  
  
Consider suggesting that to the developers.  I'm not sure what the
best list would be -- maybe pgsql-bugs if you consider the terse
message to be a bug, or maybe pgsql-hackers since it's a proposed
enhancement.

  

hackers seems like the place to go then -- I definitely don't consider
it a bug.

Thanks Michael

BJ

Re: [GENERAL] Link to development version of docs on website?

[Devrim GUNDUZ]

Hi,
On Mon, 10 Jan 2005, Chris wrote:
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?
www.PostgreSQL.org holds docs only for stable releases.
Regards,
--
Devrim GUNDUZ 
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org

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

Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

Pierre-Frédéric Caillaud wrote:

Example :
psql
create table test (id serial primary key, data10 varchar(10), data20  
varchar(20), data text );
insert into test (data10, data20, data) values ('ten','twenty','all i  
want');

python


I know that these kinds of functions are available from other languages, 
I was after an internal postgres function.  Sorry if I wasn't clear 
about that.

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

Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 05:26:59AM +1100, Brendan Jurd wrote:
 

The original problem had to do with querying a row-returning function.
I had an SQL function that returned "SETOF record", and I was trying to
use it in the FROM clause of a query.  To do so, you need to provide a
list of column definitions.  I was getting the error about the returned
row types not matching my column defs.  In the end it was a simple
mistake -- I had specified 'text' where I should have specified
'varchar'.  I had thought to use some kind of "gettype" function to find
out exactly what data types my query was returning.
   

Where would you call this gettype() function from?  It seems like
you have a chicken-and-egg situation: you need to provide a column
definition list when you issue the query, but you don't know what
the return row will look like until the query executes the function.
In the current implementation, if a function returns SETOF RECORD
then you need to know in advance what columns a particular invocation
of that function will return.
 

Not really an issue.  I could have yanked the source query out of the 
row-returning function, planted it into a regular console, and wrapped 
the hypothetical gettype() function around the individual columns to 
test the type of their output.

But that's getting away from the point.  It doesn't really matter 
whether I could have used gettype() to solve that particular problem.  
Which is why I didn't bring it up in my original post.  My post was all 
about finding out whether postgres has this functionality.  If it does, 
and I just wasn't looking hard enough, it's all good.  If it doesn't, 
I'd like to explore the possibility of getting it added in.

On that note, it might be helpful to increase the verbosity of the
"returned row types" error message, so that it actually explains the
mismatch it encountered.  Something like "Returned column 3 is
varchar(15) but column definition is text" would have made debugging a
whole lot easier.
   

Consider suggesting that to the developers.  I'm not sure what the
best list would be -- maybe pgsql-bugs if you consider the terse
message to be a bug, or maybe pgsql-hackers since it's a proposed
enhancement.
 

hackers seems like the place to go then -- I consider it an RFE rather 
than a bug.

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

Re: [GENERAL] large dbII to postgresql migration

[Jim C. Nasby]

20 million rows isn't that large, unless you've got some really wide
rows. You shouldn't have any major issues, just make sure you are
vacuuming periodically.

On Mon, Jan 10, 2005 at 06:22:34PM -0300, marcelo Cortez wrote:
> 
> hello to all 
> 
> Soon time I am going to face a migration of a DBII
> database our dear postgresql .
> I will thank for any advice that can give me, in
> addition this migration
> will have 20 million records.
> 20 million record is to large instalation, exist some
> tips for this type of facilities?
> Tia .
> Best regards
> 
>   Klingon
> 
> 
> 
> 
>   
> 
>   
>   
> ___ 
> 250MB gratis, Antivirus y Antispam 
> Correo Yahoo!, el mejor correo web del mundo 
> http://correo.yahoo.com.ar
> 
> ---(end of broadcast)---
> TIP 6: Have you searched our list archives?
> 
>http://archives.postgresql.org
> 

-- 
Jim C. Nasby, Database Consultant   [EMAIL PROTECTED] 
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---(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: [GENERAL] large dbII to postgresql migration

[Scott Marlowe]

On Mon, 2005-01-10 at 15:22, marcelo Cortez wrote:
> hello to all 
> 
> Soon time I am going to face a migration of a DBII
> database our dear postgresql .
> I will thank for any advice that can give me, in
> addition this migration
> will have 20 million records.
> 20 million record is to large instalation, exist some
> tips for this type of facilities?
> Tia .

My first recommendation would be the excellent tuning article hosted on
the varlena site:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html

My second recommendation is to read the docs thoroughly.

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] Function for retreiving datatype

[Michael Fuhr]

On Tue, Jan 11, 2005 at 08:29:33AM +1100, Brendan Jurd wrote:

> My post was all about finding out whether postgres has this
> functionality.

Certainly PostgreSQL provides a way to discover a row's column
types, but how to do it depends on where you're trying to do it
from.  If you're writing a client program in C using libpq, you
could use the functions documented under "Retrieving Query Result
Information" in the "Command Execution Functions" section of the
libpq chapter of the documentation.  If you're writing a client
program using ECPG then you could use a descriptor area.  If you're
writing a server-side C program that makes queries via SPI then you
could use the functions defined under "Interface Support Functions"
in the "Server Programming Interface" chapter.

Is that what you're looking for?

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

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

Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 08:29:33AM +1100, Brendan Jurd wrote:
 

My post was all about finding out whether postgres has this
functionality.
   

Certainly PostgreSQL provides a way to discover a row's column
types, but how to do it depends on where you're trying to do it
from.  If you're writing a client program in C using libpq, you
could use the functions documented under "Retrieving Query Result
Information" in the "Command Execution Functions" section of the
libpq chapter of the documentation.  If you're writing a client
program using ECPG then you could use a descriptor area.  If you're
writing a server-side C program that makes queries via SPI then you
could use the functions defined under "Interface Support Functions"
in the "Server Programming Interface" chapter.
Is that what you're looking for?
 

Actually I'm looking for an internal function -- something within 
postgres' implementation of SQL itself, which I can use in queries 
independent of the front-end language.  The same way you use functions 
like to_char() or now().

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[GENERAL] Limiting USAGE to only certain objects within a schema

[Chris]

Basically what I want is to limit a user to not being able to view
certain tables within a schema, in this case the public schema.   Say
we have 300 tables in a database but a particular user only needs
access to 3 of them.  I don't really want them to even be able to view
all the other tables.  Using separate databases isn't possible because
a web application running under mod perl needs access to all the
tables, and I don't want to keep a pool of persistant connections open
for 300+ databases.

I would rather avoid using schema's to do this if I can, but I think
that's the only way to get what I want.

Chris

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] to_char(0,'9999.99') -> ".00"

[Phil Endecott]

Dear All,
I was a bit surprised to find that
to_char(0,'.99')
returns .00 rather than 0.00.
Is this a bug or a feature?  How do I get what I want?
(This is with 7.4.2.  Appologies if it is a known problem.  The search 
form in the list archives seems to split words on _, so looking for 
to_char finds lots of pages that just say 'char'.)

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

[GENERAL] warning: pg_query(): Query failed

[Aaron Steele]

hi bruno,
yeah, the postgresql problem was fixed by changing IF statements to 
CASE statements in forum.module code:

// remove:
-- $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid), 
l.last_comment_timestamp, IF(l.last_comment_uid, cu.name, 
l.last_comment_name) as last_comment_name, l.last_comment_uid FROM 
{node} n ' . node_access_join_sql() . ", {node_comment_statistics} l 
/*! USE INDEX (node_comment_timestamp) */, {users} cu, {term_node} r 
WHERE n.nid = r.nid AND r.tid = %d AND n.status = 1 AND n.type = 
'forum' AND l.last_comment_uid = cu.uid AND n.nid = l.nid AND " . 
node_access_where_sql() . ' ORDER BY l.last_comment_timestamp DESC', 
$forum->tid, 0, 1));

// add:
++ $topic = db_fetch_object(db_query_range('SELECT DISTINCT(n.nid), 
l.last_comment_timestamp, CASE WHEN l.last_comment_uid = 1 THEN cu.name 
ELSE l.last_comment_name END as last_comment_name, l.last_comment_uid 
FROM {node} n ' . node_access_join_sql() . ", {node_comment_statistics} 
l, {users} cu, {term_node} r WHERE n.nid = r.nid AND r.tid = %d AND 
n.status = 1 AND n.type = 'forum' AND l.last_comment_uid = cu.uid AND 
n.nid = l.nid AND " . node_access_where_sql() . ' ORDER BY 
l.last_comment_timestamp DESC', $forum->tid, 0, 1));

thanks for all the support!
aaron
On Jan 6, 2005, at 10:25 PM, Bruno Wolff III wrote:
On Thu, Jan 06, 2005 at 17:32:30 -0800,
  Aaron Steele <[EMAIL PROTECTED]> wrote:
hi bruno,
turns out that l.last_comment_uid and l.last_comment_name are integer
and char var respectively. since i'm using 7.4.1 with strict boolean
casting, is there a better alternative to instantiating a different
version of pgsql on my server?
A better solution is fixing your code. What do you expect it to do
anyhow? Once you figure out what you want it to do, you should be able
to write a boolean expression that is true, false or null when you
want it to be. If fact from what I saw it seems that you could just use
a CASE expression and skip the function call altogether.
---(end of 
broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

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

Re: [GENERAL] PostgreSQL 8 on windows very slow

[Daniel Schuchardt]

lol wrote:
Hi,
I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adjusted. What should I check ? 
Thanks

I think first you should use a newer RC. In the first RC there were some 
probs...

With RC3 I don't have such probs. Sure If I compared Win and Linux PG 
and ma results where that LINUX is about 3 times faster under high access.

Daniel
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] Simulating WAL on separate disks

[Guy Rouillier]

We are soon to be doing performance testing.  Our testing environment
contains 4 SCSI disks in a RAID5 configuration, while our production
environment will have 6 SCSI disks, which I'm suggesting we allocate as
4 in RAID5 for data and 2 in RAID1 for WAL.

Because we don't have separate disks for WAL in our test environment,
I'd like to minimize the effect of WAL.  I've read the sections in both
the tuning guide and the base documentation, and I'm still a little
unclear about fsync and wal_sync_method.  If I set fsync FALSE, does
this disable WAL entirely?

-- 
Guy Rouillier


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [GENERAL] PostgreSQL 8 on windows very slow

[Jamie Deppeler]

Daniel Schuchardt wrote:
lol wrote:
Hi,
I'm currently testing several databases for an application written in
Delphi 7. I use zeos lib to access PostreSQL8-RC1 on MS-Windows 2000
SP4. PostrgreSQL is extremly slow, with a lot of disk access on INSERT
request. Have-you seen this problem ? May be some parameters should be
adjusted. What should I check ? Thanks
I think first you should use a newer RC. In the first RC there were 
some probs...

With RC3 I don't have such probs. Sure If I compared Win and Linux PG 
and ma results where that LINUX is about 3 times faster under high 
access.

Daniel
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

I don't have any probs with RC3 either. I was wondering if the 
PostgreSQL community has any support for gay users? Does the Linux 
version have gay mode and is it faster?

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

Re: [GENERAL] to_char(0,'9999.99') -> ".00"

[Dave Smith]

to_char(0,'9990.00')
?

On Mon, 2005-01-10 at 22:48 +, Phil Endecott wrote:
> Dear All,
> 
> I was a bit surprised to find that
> 
>   to_char(0,'.99')
> 
> returns   .00 rather than 0.00.
> 
> Is this a bug or a feature?  How do I get what I want?
> 
> (This is with 7.4.2.  Appologies if it is a known problem.  The search 
> form in the list archives seems to split words on _, so looking for 
> to_char finds lots of pages that just say 'char'.)
> 
> Regards,
> 
> Phil Endecott.
> 
> 
> ---(end of broadcast)---
> TIP 5: Have you checked our extensive FAQ?
> 
>http://www.postgresql.org/docs/faqs/FAQ.html
-- 
Dave Smith <[EMAIL PROTECTED]>


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

Re: [GENERAL] Function for retreiving datatype

[Michael Fuhr]

On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:

> Actually I'm looking for an internal function -- something within 
> postgres' implementation of SQL itself, which I can use in queries 
> independent of the front-end language.  The same way you use functions 
> like to_char() or now().

Can you provide a hypothetical example of how you'd use this function?
The problem is still vague enough to have different solutions
depending on what you're trying to do.  For example, if you want
to know the column types of a table then you could query the system
catalogs; but if you want to know the type of an arbitrary column
of an arbitrarily complex query then I'm not aware of a way to get
it in SQL (that doesn't mean there isn't a way, it just means that
I don't know of a way).  You could, however, write a C function
that takes an "anyelement" argument and returns its type's OID.
Here's an example using a trivial coltype() function that I just
wrote:

SELECT coltype(1) AS typeoid, coltype(1)::regtype AS typename;
 typeoid | typename 
-+--
  23 | integer
(1 row)

SELECT coltype(1.234) AS typeoid, coltype(1.234)::regtype AS typename;
 typeoid | typename 
-+--
1700 | numeric
(1 row)

CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT, birthday DATE);
INSERT INTO foo (name, birthday) VALUES ('Johnny', '2005-01-02');
SELECT id, coltype(id)::regtype AS idtype,
   name, coltype(name)::regtype AS nametype,
   birthday, coltype(birthday)::regtype AS birthdaytype
FROM foo;
 id | idtype  |  name  | nametype |  birthday  | birthdaytype 
+-++--++--
  1 | integer | Johnny | text | 2005-01-02 | date
(1 row)

Is that anything like what you want?  If not, then please be more
specific about a particular problem you're trying to solve.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(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: [GENERAL] Simulating WAL on separate disks

[Tom Lane]

"Guy Rouillier" <[EMAIL PROTECTED]> writes:
> We are soon to be doing performance testing.  Our testing environment
> contains 4 SCSI disks in a RAID5 configuration, while our production
> environment will have 6 SCSI disks, which I'm suggesting we allocate as
> 4 in RAID5 for data and 2 in RAID1 for WAL.

> Because we don't have separate disks for WAL in our test environment,
> I'd like to minimize the effect of WAL.  I've read the sections in both
> the tuning guide and the base documentation, and I'm still a little
> unclear about fsync and wal_sync_method.  If I set fsync FALSE, does
> this disable WAL entirely?

No, but in any case it would entirely invalidate any performance testing
you might do.  fsync off is a completely different regime of operation
(unless perhaps you have hardly any updates).

regards, tom lane

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

Re: [GENERAL] Function for retreiving datatype

[Brendan Jurd]

Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 09:31:26AM +1100, Brendan Jurd wrote:
 

Actually I'm looking for an internal function -- something within 
postgres' implementation of SQL itself, which I can use in queries 
independent of the front-end language.  The same way you use functions 
like to_char() or now().
   

Can you provide a hypothetical example of how you'd use this function?
The problem is still vague enough to have different solutions
depending on what you're trying to do.  For example, if you want
to know the column types of a table then you could query the system
catalogs; but if you want to know the type of an arbitrary column
of an arbitrarily complex query then I'm not aware of a way to get
it in SQL (that doesn't mean there isn't a way, it just means that
I don't know of a way).  You could, however, write a C function
that takes an "anyelement" argument and returns its type's OID.
Here's an example using a trivial coltype() function that I just
wrote:
SELECT coltype(1) AS typeoid, coltype(1)::regtype AS typename;
typeoid | typename 
-+--
 23 | integer
(1 row)

SELECT coltype(1.234) AS typeoid, coltype(1.234)::regtype AS typename;
typeoid | typename 
-+--
   1700 | numeric
(1 row)

CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT, birthday DATE);
INSERT INTO foo (name, birthday) VALUES ('Johnny', '2005-01-02');
SELECT id, coltype(id)::regtype AS idtype,
  name, coltype(name)::regtype AS nametype,
  birthday, coltype(birthday)::regtype AS birthdaytype
FROM foo;
id | idtype  |  name  | nametype |  birthday  | birthdaytype 
+-++--++--
 1 | integer | Johnny | text | 2005-01-02 | date
(1 row)

Is that anything like what you want?  If not, then please be more
specific about a particular problem you're trying to solve.
 

Your coltype() function is exactly what I'm looking for.  I'd envisaged 
something that takes an anyelement argument and returns the type as 
text, but returning the OID is even better.

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

Re: [GENERAL] Simulating WAL on separate disks

[Guy Rouillier]

Tom Lane wrote:
>> Because we don't have separate disks for WAL in our test environment,
>> I'd like to minimize the effect of WAL.  I've read the sections in
>> both the tuning guide and the base documentation, and I'm still a
>> little unclear about fsync and wal_sync_method.  If I set fsync
>> FALSE, does this disable WAL entirely?
> 
> No, but in any case it would entirely invalidate any performance
> testing you might do.  fsync off is a completely different regime of
> operation (unless perhaps you have hardly any updates).  

Thanks, Tom.  This app has very high insert activity.  What's the best
way to minimize the effect of WAL given that we don't have separate
disks to put it on in our test environment?

-- 
Guy Rouillier


---(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: [GENERAL] Simulating WAL on separate disks

[Tom Lane]

"Guy Rouillier" <[EMAIL PROTECTED]> writes:
> Thanks, Tom.  This app has very high insert activity.  What's the best
> way to minimize the effect of WAL given that we don't have separate
> disks to put it on in our test environment?

There's not a lot you can do about it :-(.  You can take some amount of
comfort in the idea that the numbers you get should be a lower bound on
the performance with WAL on a separate disk, though.

To some extent you are looking at this backwards.  In a high-update
situation it is WAL that is the bottleneck, by design (ie that is the
way it should be).  Rather than "minimizing the effect of WAL" what you
want to minimize is the effects of having to move the disk arms away
from the WAL file to write data files.

Thinking along that line, you want to try to avoid doing data file
writes except at checkpoints, and spread out checkpoints as far as
possible.  So bump up the checkpoint spacing parameters, and maybe
increase shared_buffers more than you normally would, and if you are
using 8.0 then disable the bgwriter.  This will result in bigger and
uglier spikes at checkpoints than the real environment, but perhaps
you can discount those in your testing.

Obviously this is just a crude approximation but it's about the best you
are going to do, given that the disk setup is fundamentally different
from what you intend to do in production.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] Function for retreiving datatype

[Michael Fuhr]

On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:

> Your coltype() function is exactly what I'm looking for.  I'd envisaged 
> something that takes an anyelement argument and returns the type as 
> text, but returning the OID is even better.
> 
> Can you please provide the source for the function?

Here's the C code:

#include "postgres.h"
#include "fmgr.h"

Datum coltype(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(coltype);

Datum
coltype(PG_FUNCTION_ARGS)
{
PG_RETURN_OID(get_fn_expr_argtype(fcinfo->flinfo, 0));
}

I lifted the expression to get the argument's type from "Polymorphic
Arguments and Return Types" in the "C-Language Functions" section
of the "Extending SQL" chapter in the documentation.  Read "Compiling
and Linking Dynamically-Loaded Functions" for build instructions.
After you've built and installed the shared object file, create the
function with the following SQL statement:

CREATE OR REPLACE FUNCTION coltype(anyelement) RETURNS oid
AS '$libdir/coltype' LANGUAGE C IMMUTABLE;

Change '$libdir/coltype' if you name the shared object file something
other than coltype.so.

Now watch, somebody will jump in and say, "Why'd you go to all that
trouble?  Here's an easier way"

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] Link to development version of docs on website?

[Pierre-Frédéric Caillaud]

The link is not there, but the doc sure is :
http://www.postgresql.org/docs/8.0/static/
Is it the right version ?
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?
Chris
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[GENERAL] Database "template1" does not exist in the system catalog.

[Aaron Mark]

When I try to connect to template1 via psql, I get the following error 
message:

psql: FATAL:  Database "template1" does not exist in the system catalog.
I get a similar error when trying to do a pg_dumpall.
In searching the list and trying to find a good place to start, I downloaded 
pgfsck and I tried the following:

# ./pgfsck -s 8192 -D data-corrupt template1
-- Detected database format 7.3
-- Table pg_class(1259):Page 0: Incorrect value in header (incorrect 
blocksize?)
-- 00 00 00 00 00 47 A7 CC 00 00 00 09 00 E8 01 5C 20 00 20 01
-- Table pg_class(1259):Page 0: Incorrect value in header (incorrect 
blocksize?)
-- 00 00 00 00 00 47 FE CC 00 00 00 09 00 E8 01 58 20 00 20 01
-- Table pg_class(1259):Page 0: Incorrect value in header (incorrect 
blocksize?)
-- 00 00 00 00 00 48 35 88 00 00 00 09 00 E8 0E E8 20 00 20 01
Couldn't find class 'pg_attribute'

I am 99% sure the block size truly is 8192, but I could be wrong; I tried 
other combinations and didn't get any better results.

I wasn't really sure what to make of this error, but noticed the 1259 and 
figured that was a file name.  I then tried downloading pg_filedump and 
tried the following:

# ./pg_filedump data-corrupt/base/1/1259
(See results below)
Am I headed in the right direction?  Any chance of recovering some/all of 
the data?

Thanks,
Aaron
*
* PostgreSQL File/Block Formatted Dump Utility - Version 1.1
*
* File: /Applications/altona/data-corrupt/base/1/1259
* Options used: None
*
* Dump created on: Tue Jan 11 04:11:26 2005
*
Block0 **
 -
Block Offset: 0x Offsets: Lower 232 (0x00e8)
Block: Size 8192  Version1Upper 348 (0x015c)
LSN:  logid  0 recoff 0x0047a7cc  Special  8192 (0x2000)
Items:   53   Free Space:  116
Length (including item array): 236
 --
Item   1 -- Length:  180  Offset: 4308 (0x10d4)  Flags: USED
Item   2 -- Length:  180  Offset: 4128 (0x1020)  Flags: USED
Item   3 -- Length:  180  Offset: 3948 (0x0f6c)  Flags: USED
Item   4 -- Length:  180  Offset: 3768 (0x0eb8)  Flags: USED
Item   5 -- Length:  180  Offset: 3588 (0x0e04)  Flags: USED
Item   6 -- Length:  180  Offset: 3408 (0x0d50)  Flags: USED
Item   7 -- Length:  180  Offset: 3228 (0x0c9c)  Flags: USED
Item   8 -- Length:  152  Offset: 8040 (0x1f68)  Flags: USED
Item   9 -- Length:  180  Offset: 3048 (0x0be8)  Flags: USED
Item  10 -- Length:  180  Offset: 2868 (0x0b34)  Flags: USED
Item  11 -- Length:  180  Offset: 2688 (0x0a80)  Flags: USED
Item  12 -- Length:  180  Offset: 2508 (0x09cc)  Flags: USED
Item  13 -- Length:  180  Offset: 2328 (0x0918)  Flags: USED
Item  14 -- Length:  180  Offset: 2148 (0x0864)  Flags: USED
Item  15 -- Length:  180  Offset: 1968 (0x07b0)  Flags: USED
Item  16 -- Length:  180  Offset: 1788 (0x06fc)  Flags: USED
Item  17 -- Length:  180  Offset: 1608 (0x0648)  Flags: USED
Item  18 -- Length:  180  Offset: 1428 (0x0594)  Flags: USED
Item  19 -- Length:  180  Offset: 1248 (0x04e0)  Flags: USED
Item  20 -- Length:  180  Offset: 1068 (0x042c)  Flags: USED
Item  21 -- Length:  180  Offset:  888 (0x0378)  Flags: USED
Item  22 -- Length:  180  Offset:  708 (0x02c4)  Flags: USED
Item  23 -- Length:  180  Offset:  528 (0x0210)  Flags: USED
Item  24 -- Length:  180  Offset:  348 (0x015c)  Flags: USED
Item  25 -- Length:0  Offset: 4460 (0x116c)  Flags: 0x00
Item  26 -- Length:0  Offset: 4312 (0x10d8)  Flags: 0x00
Item  27 -- Length:0  Offset: 4164 (0x1044)  Flags: 0x00
Item  28 -- Length:0  Offset: 4016 (0x0fb0)  Flags: 0x00
Item  29 -- Length:0  Offset: 3868 (0x0f1c)  Flags: 0x00
Item  30 -- Length:  148  Offset: 7892 (0x1ed4)  Flags: USED
Item  31 -- Length:  148  Offset: 7744 (0x1e40)  Flags: USED
Item  32 -- Length:  148  Offset: 7596 (0x1dac)  Flags: USED
Item  33 -- Length:  148  Offset: 7448 (0x1d18)  Flags: USED
Item  34 -- Length:  148  Offset: 7300 (0x1c84)  Flags: USED
Item  35 -- Length:  148  Offset: 7152 (0x1bf0)  Flags: USED
Item  36 -- Length:  148  Offset: 7004 (0x1b5c)  Flags: USED
Item  37 -- Length:  148  Offset: 6856 (0x1ac8)  Flags: USED
Item  38 -- Length:  148  Offset: 6708 (0x1a34)  Flags: USED
Item  39 -- Length:  148  Offset: 6560 (0x19a0)  Flags: USED
Item  40 -- Length:  148  Offset: 6412 (0x190c)  Flags: USED
Item  41 -- Length:  148  Offset: 6264 (0x1878)  Flags: USED
Item  42 -- Length:  148  Offset: 6116 (0x17e4)  Flags: USED
Item  43 -- Length:  148  Offset: 5968 (0x1750)  Flags: USED
Item  44 -- Length:  148  Offset: 5820 (0x16bc)  Flags: USED
Item  45 -- Length:  148  Offset: 5672 (0x1628)  Flags: USED
Item  46 -- Length:  148  Offset: 5524 (0x1594)  Flags: USED
Item  47 -- Length:  148  Offset: 5376 (0x1500)  Flags: USED
Item  48 -- Length:  148  Offset: 5228 (0x146c)  Flags: USED
Item  49 -- Length:  148  Offset: 5080 (0x13d8)  Flags: USED
Item 

[GENERAL] Database "template1" does not exist in the system catalog

[Aaron Mark]

I apologize if this is a duplicate of a message I just sent.  The message 
didn't appear to go through.  I seem to have encountered a major corruption 
error.  I am currently running PostgreSQL 7.3.6.  Here is an example of an 
error I received:

# psql -h 127.0.0.1 -p 5432 -U postgres template1
psql: FATAL:  Database "template1" does not exist in the system catalog.
In digging around the list, I have tried running pgfsck:
# pgfsck -s 8192 -D /Applications/altona/data-corruption template1
-- Detected database format 7.3
-- Table pg_class(1259):Page 0: Incorrect value in header (incorrect 
blocksize?)
-- 00 00 00 00 00 47 A7 CC 00 00 00 09 00 E8 01 5C 20 00 20 01
-- Table pg_class(1259):Page 0: Incorrect value in header (incorrect 
blocksize?)
-- 00 00 00 00 00 47 FE CC 00 00 00 09 00 E8 01 58 20 00 20 01
-- Table pg_class(1259):Page 0: Incorrect value in header (incorrect 
blocksize?)
-- 00 00 00 00 00 48 35 88 00 00 00 09 00 E8 0E E8 20 00 20 01
Couldn't find class 'pg_attribute'

I'm 99% sure the block size is 8192, but I could be wrong.  I proceeded to 
try out pg_filedump on data-corrupt/base/1/1259, but I didn't see anything 
out of the ordinary.  Can't say that I know what I was looking for, though.

Any ideas on what I can try to potentially fix this problem and/or recover 
any of this data?

Thanks,
Aaron
_
Express yourself instantly with MSN Messenger! Download today - it's FREE! 
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/

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

Re: [GENERAL] Database "template1" does not exist in the system catalog.

[Tom Lane]

"Aaron Mark" <[EMAIL PROTECTED]> writes:
> When I try to connect to template1 via psql, I get the following error 
> message:
> psql: FATAL:  Database "template1" does not exist in the system catalog.

Hm, can you connect to any other databases?  If so, what does "select *
from pg_database" show?

regards, tom lane

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

Re: [GENERAL] Link to development version of docs on website?

[Peter Childs]

Devrim GUNDUZ wrote:
Hi,
On Mon, 10 Jan 2005, Chris wrote:
I know they can be accessed at developer.postgresql.org, but I didn't
see a link to the docs for postgresql 8 on the new website, did I miss
it somewhere?

www.PostgreSQL.org holds docs only for stable releases.
Regards,
--
Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, 
devrim.gunduz~linux.org.tr
http://www.tdmsoft.com http://www.gunduz.org

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html
   However
http://developer.postgresql.org/docs/postgres/index.html
   has the Beta docs, Its under Testing just above Beta Information on 
the developer web site.

   Very Very Important if you are beta testing since quite a few things 
have changed for 8.0. Now why is RC4 crashing on my debain box. 
Currently its causing a panic in ext3 mid dump load I'll post more 
details later...

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

Re: [GENERAL] Database "template1" does not exist in the system catalog.

[Aaron Mark]

Unfortunately, I can't seem to connect to any other database.  I only had 
one non-system database so I tried that, and that I was thinking there was a 
template0, so I tried that as well.

Thanks in advance for any help you can provide!
Aaron
From: Tom Lane <[EMAIL PROTECTED]>
To: "Aaron Mark" <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Database "template1" does not exist in the system 
catalog. Date: Tue, 11 Jan 2005 00:33:09 -0500

"Aaron Mark" <[EMAIL PROTECTED]> writes:
> When I try to connect to template1 via psql, I get the following error
> message:
> psql: FATAL:  Database "template1" does not exist in the system catalog.
Hm, can you connect to any other databases?  If so, what does "select *
from pg_database" show?
regards, tom lane
---(end of broadcast)---
TIP 8: explain analyze is your friend
_
FREE pop-up blocking with the new MSN Toolbar – get it now! 
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/

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

Re: [GENERAL] Function for retreiving datatype

[Joe Conway]

Michael Fuhr wrote:
On Tue, Jan 11, 2005 at 11:00:15AM +1100, Brendan Jurd wrote:
Your coltype() function is exactly what I'm looking for.  I'd envisaged 
something that takes an anyelement argument and returns the type as 
text, but returning the OID is even better.
[...snip slick function...]
Now watch, somebody will jump in and say, "Why'd you go to all that
trouble?  Here's an easier way"
Not exactly a drop in replacement, but you could check whether you have 
one of set of types with the undocumented* IS OF construct:

regression=# select prosrc is of (text) from pg_proc limit 1;
 ?column?
--
 t
(1 row)
regression=# select prosrc is of (bytea) from pg_proc limit 1;
 ?column?
--
 f
(1 row)
regression=# select prosrc is of (bytea,text) from pg_proc limit 1;
 ?column?
--
 t
(1 row)
Also note that in PL/pgSQL, you can use %TYPE to create a variable to 
the same type as an argument:

  "%TYPE is particularly valuable in polymorphic functions, since the
   data types needed for internal variables may change from one call to
   the next. Appropriate variables can be created by applying %TYPE to
   the function's arguments or result placeholders."
http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-TYPE
And a variable can also be created with the function's runtime-resolved 
return type:

  "When the return type of a PL/pgSQL function is declared as a
   polymorphic type (anyelement or anyarray), a special parameter $0 is
   created. Its data type is the actual return type of the function, as
   deduced from the actual input types (see Section 33.2.5). This allows
   the function to access its actual return type as shown in Section
   37.4.2. $0 is initialized to null and can be modified by the
   function, so it can be used to hold the return value if desired,
   though that is not required. $0 can also be given an alias."
http://www.postgresql.org/docs/7.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-ALIASES
Joe
* search the archives if you want the background as to why it is still 
undocumented -- in short, it is close to, but not quite SQL99 compliant, 
and although I had hoped to fix that "not quite" part, I've yet to find 
the time :(

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

[GENERAL] datestyle formatting

[mstory]

I need to know the day of the week for a schedule rotation algorithm i'm working
on.  Initially i was going to use the function for day of the year and week of
the year to find the first day of the year, but it seems like it would be easier
to use the postgres, dmy format and then parse the string for the day
abbreviation.  I've switched the datestyle format in the .conf file and used the
set to command, but my output never reads as the example in the 7.4.6 docs it
always shows just the dmy standard output 10-12-1999 for example as opposed to
wed oct 17 1987, any help on this would be appreciated

thanks,
matt

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

Re: [GENERAL] datestyle formatting

[Tom Lane]

[EMAIL PROTECTED] writes:
> I need to know the day of the week for a schedule rotation algorithm
> i'm working on.

extract(dow ...) might help.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [GENERAL] datestyle formatting

[Michael Glaesemann]

On Jan 11, 2005, at 15:03, [EMAIL PROTECTED] wrote:
I need to know the day of the week for a schedule rotation algorithm  
i'm working
on.  Initially i was going to use the function for day of the year and  
week of
the year to find the first day of the year, but it seems like it would  
be easier
to use the postgres, dmy format and then parse the string for the day
abbreviation.  I've switched the datestyle format in the .conf file  
and used the
set to command, but my output never reads as the example in the 7.4.6  
docs it
always shows just the dmy standard output 10-12-1999 for example as  
opposed to
wed oct 17 1987, any help on this would be appreciated

Check out to_char(). It'll probably help you get to where you want to  
go.



Michael Glaesemann
grzm myrealbox com
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster