Re: [GENERAL] Vacuum and Transactions
From: Trewern, Ben [EMAIL PROTECTED] If there is a transaction running when 'vacuumdb -a -z' is run (as a cron job) it stops running at that database till the transaction completes. That is not so much of a problem until a new client tries to connect to the database. This new connection hangs, waiting for the vacuum to complete. This situation is not all that helpful and means I have to be careful at what time I run vacuum so it does not interfere with new clients. Is this a bug or the standard way in which postgres works and are there any plans change this? Would vacuuming the tables one at a time not help here? It'd mean a small script to read a list of databases/tables out of PG but should reduce the impact on your clients (if I'm thinking straight here) - Richard Huxton ---(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] Newbie DBD::Pg question
Hi, Extraction of large amount of data is not realistic especially with CGI, then DBI/DBD::Pg always return all data received from a query so if you don't want to tired your machine, the better way is to use cursor in a transaction (or query) or the LIMIT+OFFSET keywords. Regards Gilles DAROLD Mithun Bhattacharya wrote: Just started on postgres a few days back and was having a few questions about the perl interface to it. According to perldoc DBD::Pg Cursors Although PostgreSQL has a cursor concept, it has not been used in the current implementation. Cursors in PostgreSQL can only be used inside a transaction block. Because only one transaction block at a time is allowed, this would have implied the restriction, not to use any nested SELECT statements. Hence the execute method fetches all data at once into data structures located in the frontend application. This has to be considered when selecting large amounts of data ! - Frankly speaking I am not sure what the Author is saying here. Anybody cares to point me in the right direction ??? I do have to extract large amount of data but I am not sure whether it affects me or not. Mithun ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] query syntax change?
Ed Loehr writes: This query works in 7.0.3... SELECT p.*, e.id AS employee_id, e.ref_name, e.business_line_id, e.record_status_id AS emp_record_status_id FROM person p, employee e WHERE e.person_id = p.id UNION ALL SELECT p.*, NULL AS employee_id, NULL AS ref_name, NULL AS business_line_id, NULL AS emp_record_status_id FROM person p WHERE NOT EXISTS (SELECT id FROM employee WHERE person_id = p.id) ORDER BY p.sortable_last_name; but in 7.1.2 it reports the following error: ERROR: Relation 'p' does not exist There wording of the error message isn't the greatest, but the cause is that the p is not visible to the ORDER BY. Consider, what if the p in the two union branches where different tables? The SQL-legal namespace in ORDER BY is the column aliases of the output columns in the select list, so that would be sortable_last_name (chosen as default due to lack of alias), employee_id, ref_name, etc. In non-unioned queries we can be a little more lax about this because the semantics are clear. Btw., order by + union doesn't work prior to 7.1 anyway. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] SELECT'ing a function call
I would like to be able to define a table with 1 column containing user
names,
and the next column being a dynamically generating function that returns a
user's quota.
I think I see how to create the shared object, and the SQL function. But,
how
do I create a table that uses a function for a column based on the value of
another
column.
For example, I tried:
CREATE TABLE test (
num1 INTEGER,
num2 INTEGER DEFAULT add_one('num1')
);
But that said that pg_atoi doesn't understand num1 -- so apprenting atoi is
interpreting
num1 as a string, so I tried ...DEFAULT add_one(num1) which gave a different
error.
Is this even possible? To have a trigger based on a select statement. I
looked at the SQL
TRIGGER command and it seems to only work for INSERT, UPDATE and DELETE. I
need a trigger
for SELECT's.
Thanks,
Jared H.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
[GENERAL] Number of days
Is there a function that returns the number of days between two dates? I found only age, which calculates the difference between two timestamps, but the example shows the result as a timestamp. Thanks, Mihai Gheorghiu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[GENERAL] orphaned RI constraints
I'm working with two different postgres installations - they're both v7.1.2. On one I can drop a table and the related constraint info seems to go away with the table, on the other the restraint trigger seems to remain in the system tables and then when I try to delete rows from tables once related to the dropped table I get errors saying the dropped tabled doesn't exist - which I'm interpreting as the RI trigger trying to do its thing and failing. Questions: 1) Is this possible, or should I look for another explanation. 2) Can I fix things by dropping the constraint info from the system tables, if so how? Is there a function which cleans the system tables checking for problems like orphaned triggers, functions and sequences? Thanks, Andy. ---(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] Number of days
Mihai Gheorghiu [EMAIL PROTECTED] writes: Is there a function that returns the number of days between two dates? Subtraction. regression=# select '27 Feb 2001'::date - '5 Jan 2001'::date; ?column? -- 53 (1 row) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] orphaned RI constraints
Somazx Interesting [EMAIL PROTECTED] writes: I'm working with two different postgres installations - they're both v7.1.2. On one I can drop a table and the related constraint info seems to go away with the table, on the other the restraint trigger seems to remain in the system tables and then when I try to delete rows from tables once related to the dropped table I get errors saying the dropped tabled doesn't exist - which I'm interpreting as the RI trigger trying to do its thing and failing. IIRC, pg_dump scripts made by 7.1 pg_dump did not dump the FROM part of the trigger definition, so dropping the referenced table of an RI trigger reloaded from such a dump didn't make the trigger go away. This is fixed in 7.1.2 (not sure about 7.1.1). 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] orphaned RI constraints
On Fri, 6 Jul 2001, Somazx Interesting wrote: I'm working with two different postgres installations - they're both v7.1.2. On one I can drop a table and the related constraint info seems to go away with the table, on the other the restraint trigger seems to remain in the system tables and then when I try to delete rows from tables once related to the dropped table I get errors saying the dropped tabled doesn't exist - which I'm interpreting as the RI trigger trying to do its thing and failing. Questions: 1) Is this possible, or should I look for another explanation. 2) Can I fix things by dropping the constraint info from the system tables, if so how? Is there a function which cleans the system tables checking for problems like orphaned triggers, functions and sequences? Is it possible that the one that's failing was restored from an old dump output? I believe there was a problem (I think resolved) where the triggers lost track of the other table involved after a dump/restore which could have this effect. To fix it, you should be able to use DROP TRIGGER on the appropriate triggers that were created (you can find these through a select on pg_trigger, using the tgargs to find the appropriate ones). As a warning, you need to double quote the trigger name, so for example if you saw the following rows for the constraint: 782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true | true | unnamed |782372 | false| false | 6 || unnamed\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true | true | unnamed |782372 | false| false | 6 || unnamed\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 you should be able to do DROP TRIGGER RI_ConstraintTrigger_782384; DROP TRIGGER RI_ConstraintTrigger_782386; ---(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] orphaned RI constraints
At 01:30 PM 7/6/2001 -0700, you wrote: On Fri, 6 Jul 2001, Somazx Interesting wrote: I'm working with two different postgres installations - they're both v7.1.2. On one I can drop a table and the related constraint info seems to go away with the table, on the other the restraint trigger seems to remain in the system tables and then when I try to delete rows from tables once related to the dropped table I get errors saying the dropped tabled doesn't exist - which I'm interpreting as the RI trigger trying to do its thing and failing. Is it possible that the one that's failing was restored from an old dump output? I believe there was a problem (I think resolved) where the triggers lost track of the other table involved after a dump/restore which could have this effect. I think that is exactly what happened. Tom mentioned 7.1 had that problem and until yesterday the development server was still v7.1 To fix it, you should be able to use DROP TRIGGER on the appropriate triggers that were created (you can find these through a select on pg_trigger, using the tgargs to find the appropriate ones). As a warning, you need to double quote the trigger name, so for example if you saw the following rows for the constraint: 782359 | RI_ConstraintTrigger_782384 | 1654 | 9 | true | true | unnamed |782372 | false| false | 6 || unnamed\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 782359 | RI_ConstraintTrigger_782386 | 1655 | 17 | true | true | unnamed |782372 | false| false | 6 || unnamed\000qqq2\000qqq\000UNSPECIFIED\000a\000a\000 you should be able to do DROP TRIGGER RI_ConstraintTrigger_782384; DROP TRIGGER RI_ConstraintTrigger_782386; Perfect. I'll do this, thanks for the help. Andy. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Newbie DBD::Pg question
I haven't gotten into it yet but you could try using the sendQuery method and looping on the new data as it becomes available. Josh On Fri, 6 Jul 2001, Mithun Bhattacharya wrote: Just started on postgres a few days back and was having a few questions about the perl interface to it. According to perldoc DBD::Pg Cursors Although PostgreSQL has a cursor concept, it has not been used in the current implementation. Cursors in PostgreSQL can only be used inside a transaction block. Because only one transaction block at a time is allowed, this would have implied the restriction, not to use any nested SELECT statements. Hence the execute method fetches all data at once into data structures located in the frontend application. This has to be considered when selecting large amounts of data ! - Frankly speaking I am not sure what the Author is saying here. Anybody cares to point me in the right direction ??? I do have to extract large amount of data but I am not sure whether it affects me or not. Mithun ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Vacuum and Transactions
Bruce Momjian [EMAIL PROTECTED] writes: In 7.2, VACUUM will not require an exclusive lock. Care to elaborate on that? How are you going to do it? Uh, have you not been paying attention to pg-hackers for the last two months? I am assuming here that concurrent VACUUM will become the default kind of vacuum, and the old style will be invoked by some other syntax (VACUUM FULL ..., maybe). By concurrent vacuum, do you mean the auto-vacuum you are doing? I realize that will not need a lock. Are you changing default VACUUM so it only moves rows inside existing blocks too? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Vacuum and Transactions
Bruce Momjian [EMAIL PROTECTED] writes: Are you changing default VACUUM Only to the extent of not being the default. 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'ing a function call
Command: create rule
Description: define a new rule
Syntax:
CREATE RULE rule_name AS ON
{ SELECT | UPDATE | DELETE | INSERT }
TO object [WHERE qual]
DO [INSTEAD] [action|NOTHING|[actions]];
HTH
Omid
From: Jared H. Hudson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: [GENERAL] SELECT'ing a function call
Date: Fri, 6 Jul 2001 12:25:21 -0500
I would like to be able to define a table with 1 column containing user
names,
and the next column being a dynamically generating function that returns a
user's quota.
I think I see how to create the shared object, and the SQL function. But,
how
do I create a table that uses a function for a column based on the value of
another
column.
For example, I tried:
CREATE TABLE test (
num1 INTEGER,
num2 INTEGER DEFAULT add_one('num1')
);
But that said that pg_atoi doesn't understand num1 -- so apprenting atoi is
interpreting
num1 as a string, so I tried ...DEFAULT add_one(num1) which gave a
different
error.
Is this even possible? To have a trigger based on a select statement. I
looked at the SQL
TRIGGER command and it seems to only work for INSERT, UPDATE and DELETE. I
need a trigger
for SELECT's.
Thanks,
Jared H.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
_
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.
---(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] order by + union (was: query syntax change?)
Peter Eisentraut wrote: Ed Loehr writes: This query works in 7.0.3... SELECT p.*, e.id AS employee_id, e.ref_name, e.business_line_id, e.record_status_id AS emp_record_status_id FROM person p, employee e WHERE e.person_id = p.id UNION ALL SELECT p.*, NULL AS employee_id, NULL AS ref_name, NULL AS business_line_id, NULL AS emp_record_status_id FROM person p WHERE NOT EXISTS (SELECT id FROM employee WHERE person_id = p.id) ORDER BY p.sortable_last_name; but in 7.1.2 it reports the following error: ERROR: Relation 'p' does not exist There wording of the error message isn't the greatest, but the cause is that the p is not visible to the ORDER BY. Consider, what if the p in the two union branches where different tables? The SQL-legal namespace in ORDER BY is the column aliases of the output columns in the select list, so that would be sortable_last_name (chosen as default due to lack of alias), employee_id, ref_name, etc. In non-unioned queries we can be a little more lax about this because the semantics are clear. Thanks, that makes sense. Btw., order by + union doesn't work prior to 7.1 anyway. Looks like order by + union was enabled at least in 7.0.3, fwiw... emsdb=# select version(); version - PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 (1 row) emsdb=# drop table mytable; from mytable t where t.name isnull union all select t.* from mytable t where t.name notnull order by id; DROP emsdb=# create table mytable (id integer not null, name varchar); CREATE emsdb=# insert into mytable values (1,'not-null'); INSERT 31802775 1 emsdb=# insert into mytable values (3,null); INSERT 31802776 1 emsdb=# insert into mytable values (2,'not-null'); INSERT 31802777 1 emsdb=# emsdb=# select t.* emsdb-# from mytable t emsdb-# where t.name isnull emsdb-# union all emsdb-# select t.* emsdb-# from mytable t emsdb-# where t.name notnull emsdb-# emsdb-# order by id; id | name +-- 1 | not-null 2 | not-null 3 | (3 rows) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Vacuum and Transactions
In 7.2, VACUUM will not require an exclusive lock. Care to elaborate on that? How are you going to do it? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Re: [SQL] Cross database foreign keys
Morgan Curley writes: Does anyone know if it is possible to connect to a differernt db from within a plsql function. I have multilple inter-related schemas and want to enforce some fk relationships. Not possible -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] Vacuum and Transactions
Bruce Momjian [EMAIL PROTECTED] writes: In 7.2, VACUUM will not require an exclusive lock. Care to elaborate on that? How are you going to do it? Uh, have you not been paying attention to pg-hackers for the last two months? I am assuming here that concurrent VACUUM will become the default kind of vacuum, and the old style will be invoked by some other syntax (VACUUM FULL ..., maybe). 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
[GENERAL] Re: [HACKERS] 2 gig file size limit
* Naomi Walker [EMAIL PROTECTED] [010706 17:57]: If PostgreSQL is run on a system that has a file size limit (2 gig?), where might cause us to hit the limit? PostgreSQL is smart, and breaks the table files up at ~1GB per each, so it's transparent to you. You shouldn't have to worry about it. LER -- Naomi Walker Chief Information Officer Eldorado Computing, Inc. 602-604-3100 ext 242 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED] US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749 ---(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] order by + union (was: query syntax change?)
Ed Loehr [EMAIL PROTECTED] writes: Btw., order by + union doesn't work prior to 7.1 anyway. Looks like order by + union was enabled at least in 7.0.3, fwiw... Nonetheless, it had bugs crawling out of it everywhere ... try more complex test cases, and pay attention to whether you actually get the right answer (like the right sort order, distinct/not distinct, etc). Cross-datatype cases tended to coredump, too. I believe this mess is all fixed as of 7.1. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] HUPing a database
On Fri, Jul 06, 2001 at 06:15:47PM +0200, Peter Eisentraut wrote: : Philip Molter writes: : : I'm doing logging of PG information, and I'd like to rotate the logs. : What's the proper way to HUP the server once the logs have been : rotated? : : There isn't one. You're better off piping the postmaster output through a : separate rotate program. Hrmm, are there plans to implement this feature? That seems like an *EXTREMELY* large oversight, to not allow for a simple HUP for something as universal as daily log rotation. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
